SQL Dates Selection

Question

I Have a OPL_Dates Table with Start Date and End Dates as Below:

dbo.OPL_Dates
ID       Start_date       End_date
--------------------------------------
12345    1975-01-01       2001-12-31
12345    1989-01-01       2004-12-31
12345    2005-01-01       NULL
12345    2007-01-01       NULL
12377    2009-06-01       2009-12-31
12377    2013-02-07       NULL
12377    2010-01-01       2012-01-01
12489    2011-12-31       NULL
12489    2012-03-01       2012-04-01

The Output I am looking for is:

ID       Start_date       End_date
-------------------------------------
12345    1975-01-01       2004-12-31
12345    2005-01-01       NULL
12377    2009-06-01       2009-12-31
12377    2010-01-01       2012-01-01
12377    2013-02-07       NULL
12489    2011-12-31       NULL

Basically, I want to show the gap between the OPL periods(IF Any) else I need min of Start Date and Max of End Dates, for a particular ID.NULL means Open-Ended Date which can be converted to "9999-12-31".

@maSTAShuFu Thanks For the Reply,I din't completely get your Question but , start and End dates for every ID if there is a gap in his period. For 12345 I want his Start date which is 1975-01-01 and his end date which is 2004-12-31 because the start date 1989-01-01 is already covered in the above time period and then the start date 2005-01-01 to end date "NULL" as the start date 2007-01-01 also falls under the time period 2005-01-01 through NULL. — chennam
– chennam, Commented Apr 22, 2018 at 21:36
@maSTAShuFu I've added an other scenario. Please take a look. Let me know if you have any questions. — chennam
– chennam, Commented Apr 22, 2018 at 21:39
Hi, welcome to SO. If you have written code for this that you can't get to work, then you have come to the right place. Just edit your question and add the relevant parts of your code into it, because without that we cannot help. Also please see How to Ask. — Peter B
– Peter B, Commented Apr 22, 2018 at 21:42

Gordon Linoff · Accepted Answer · 2018-04-22 23:00:38Z

The following pretty much does what you want:

with p as (
      select v.*, sum(inc) over (partition by v.id order by v.dte) as running_inc
      from t cross apply
           (values (id, start_date, 1),
                   (id, coalesce(end_date, '2999-12-31'), -1)
           ) v(id, dte, inc)
     )
select id, min(dte), max(dte)
from (select p.*, sum(case when running_inc = 0 then 1 else 0 end) over (partition by id order by dte desc) as grp
      from p
     ) p
group by id, grp;

Note that it changes the "inifinite" end date from NULL to 2999-12-31. This is a convenience, because NULL orders first in SQL Server ascending sorts.

Here is a SQL Fiddle.

What is this doing? It is unpivoting the dates into a single column, with a 1/-1 flag (inc) indicating whether the record is a start or end. The running sum of this flag then indicates the groups that should be combined. When the running sum is 0, then a group has ended. To include the end date in the right group, a reverse running sum is needed -- but that's a detail.

Collectives™ on Stack Overflow

SQL Dates Selection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related