• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

combining rows with dates across multiple rows - A SQL question

I'm having trouble combining rows with dates across more than one rows with no breaks
in time:



      Key                              eff_dt                       term_dt

     1234                             02/01/2012                 03/01/2012
      1234                            03/02/2012                 12/31/9999

Result set should be:

      1234                            02/01/2012                  12/31/9999

If this situation:

       5678                           02/01/2012                   03/01/2012
       5678                           03/03/2012                   12/31/9999

Since there is a one day not covered both rows  (03/02/2012 not included) would be in the result set.
1 Solution
It's not elegant but I think this should work...
create table #tmp1 (key int, eff_dt date, term_dt date)
-- you could add indexes on the two tmp tables if you have large enough data sets
select a.key, a.eff_dt, b.term_dt into #tmp1 from TableA a, TableA b
where a.key = b.key and dateadd(day,a.term_dt,1) = b.eff_dt
select * into #tmp1 from TableA a
where not exists (select 1 from TableA b where a.key = b.key and dateadd(day,a.term_dt,1) = b.eff_dt)
and not exists (select 1 from #tmp1 t where a.key = t.key and a.term_dt = t.term_dt)

Open in new window

If you can have more than one level of "continuation" then it gets uglier and adds a lot more sql (basically add a #tmp2 and keep selecting back and forth between #tmp1 and #tmp2 combining rows (and clearing the "destination" table between each time) until the rowcount is the same between #tmp1 and #tmp2).

This would likely be simpler in Transact-SQL but it's been a while since I've done much with T-SQL.

This would be very easy to do in most programming languages (eg select the full resultset from TableA and combine in your language of choice).

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now