combining rows with dates across multiple rows  - A SQL question

Posted on 2012-09-17
Last Modified: 2012-09-17
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.
Question by:garyinmiami2003
    1 Comment
    LVL 26

    Accepted 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).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
    PRTG Network Monitor lets you monitor your bandwidth usage, so you know who is using up your bandwidth, and what they're using it for.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now