postgresql - Join 2 tables where two sets of numbers overlap within the joining columns -


i need join 2 tables postgresql 2 sets of numbers overlap within joining columns.

the image below explains - needing take table of congresspeople , party affiliation , join table of districts (based on when districts drawn or redrawn). result rows show dates district, state , congressperson same. wherever there dates of district known , congressperson dates unknown, dates known district filled portion, , dates congressperson left blank - , vice versa.

enter image description here

for example, first rows in tables:

congressperson table:

arkansas, district 5, republican: 1940-1945

district table:

arkansas, district 5: 1942-1963

results in following combinations (start_comb , end_comb):

1940-1942 1942-1945

and combination district unknown (1940-1942), district dates left blank.

the final set of date columns (gray) combinations district (this super easy).

in case you're wondering for, creating animated map, kind of this, congressional districts on time: https://www.youtube.com/watch?v=vqdyn04vtf8

i'll end there map every known district, there known or unknown party.

haven't got far, did:

select * congressperson join districts on start_dist between start_cong , end_cong district.a = district.b or end_dist between start_cong , start_dist or start_cong = start_dist or end_cong= end_dist;

the idea make list of unique dates both tables first. each such date find next date (in particular case dates grouped state, district, , next date looked particular state, district).
have list of ranges looking for. can join (for paticular task left join) other tables required conditions:

select     r.state,     c.start_cong,     c.end_cong,     c.party,     coalesce(c.district, d.district) district,     d.start_dist,     d.end_dist,     start_comb,     end_comb,     case when d.district not null start_comb end final_start,     case when d.district not null end_comb end final_end (     dates (         select             *         (             select                  c.state,                 c.district,                 start_cong date             congressperson c             union              select                 c.state,                 c.district,                  end_cong             congressperson c             union              select                  d.state,                 d.district,                 start_dist             district d              union              select                 d.state,                 d.district,                  end_dist             district d          ) dates         group              state,             district,             date         order              state,             district,                 date     )      select         dates.state,         dates.district,         dates.date start_comb,     (select          d.date               dates d             d.state = dates.state ,         d.district = dates.district ,         d.date > dates.date     order          d.date     limit 1     ) end_comb              dates) r left join congressperson c on                              c.state = r.state ,                             c.district = r.district ,                             start_comb between c.start_cong , c.end_cong ,                              end_comb between c.start_cong , c.end_cong left join district d on                          d.state = r.state ,                         d.district = r.district ,                         start_comb between d.start_dist , d.end_dist ,                          end_comb between d.start_dist , d.end_dist     end_comb not null  order      r.state, coalesce(c.district, d.district), start_comb, end_comb, start_cong, end_cong

-

Comments

Post a Comment

Popular posts from this blog

c# - ajax - How to receive data both html and json from server? -

i have referenced this topic , doesn't solve problem. i'm using mvc 5 c#. have used code receive data server data type json . $.ajax({ url: "/mycontroller/myaction", type: "post", datatype: "json", success: function (data) { if (data.result) { alert('successfull'); } else { alert(data.ex); } } }); and controller code: [httppost] public actionresult myaction() { try { return json(new { result = "true", ex = "" }); } catch (exception e) { return json(new { result = "false", ex = e.message }); } } i use way data type html: $.ajax({ url: "/mycontroller/myaction", type: "post", datatype: "html", success: function (data) { $(".mydiv").append(data); } }); and controller should be: [httppost] public actionresult myaction() { return partialview("_...
Read more

swift - Button on Table View Cell connected to local function -

i have table view cell loaded data parse request. loads "friend requests", showing requesting friends name , 2 buttons, "accept" , "reject" here cellforrowatindexpath func: func tableview(tableview: uitableview, cellforrowatindexpath indexpath: nsindexpath) -> uitableviewcell { let cell:notifications_jlr_cell = self.tableview.dequeuereusablecellwithidentifier("cell") as! notifications_jlr_cell cell.txtrequestdesc.text = searchresults[indexpath.row].requesteename + " has requested friend" cell.btnapprove_outlet.addtarget(self, action:selector("approve"), forcontrolevents: uicontrolevents.touchupinside) return cell } the table loads fine, , addtarget line, once "approve" button clicked, runs func: func approve() { println("approve") } however, when fetching parse data, have set in array, objectid friend request, want refer in approve() function how can pass objectid ...
Read more