CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2005
Sandip Bantawa
Sandip Bantawa

Reputation: 2880

Finding occupied dates from date ranges

This is the data that I have on SQL-Sever 2005 mode

ID              FromDate    ToDate        Diff

ZIM145876-01    03/01/2011  02/29/2012    1
ZIM145876-01    03/01/2012  02/28/2013    1
ZIM145876-01    03/01/2013  02/28/2014    NULL


ZIM145881-02    02/01/2012  03/31/2012    1
ZIM145881-02    04/01/2012  06/30/2012    1
ZIM145881-02    07/01/2012  09/30/2012    1
ZIM145881-02    10/01/2012  03/31/2013    1
ZIM145881-02    04/01/2013  06/30/2013    NULL


ZIM145878-01    05/15/2010  05/14/2011    201
ZIM145878-01    12/01/2011  11/30/2012    1
ZIM145878-01    12/01/2012  11/30/2013    NULL

Now on first case I want to have 

ZIM145876-01    03/01/2011  02/28/2014

ZIM145881-02    02/01/2012  06/30/2013

However in 3rd case we have two occupied dates for same IDs and this is what I want

ZIM145878-01    05/15/2010  05/14/2011
ZIM145878-01    12/01/2011  11/30/2013

So any hint would be highly appreciated (SQLFiddle)

Upvotes: 0

Views: 117

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

This seems to do the job. Unfortunately, it's not going to be too efficient on large data sets:

declare @t table (ID varchar(50),FromDate date,ToDate date, Diff int)
insert into @t(ID,FromDate,ToDate,Diff) values
('ZIM145876-01','20110301','20120229',1   ),
('ZIM145876-01','20120301','20130228',1   ),
('ZIM145876-01','20130301','20140228',NULL),
('ZIM145881-02','20120201','20120331',1   ),
('ZIM145881-02','20120401','20120630',1   ),
('ZIM145881-02','20120701','20120930',1   ),
('ZIM145881-02','20121001','20130331',1   ),
('ZIM145881-02','20130401','20130630',NULL),
('ZIM145878-01','20100515','20110514',201 ),
('ZIM145878-01','20111201','20121130',1   ),
('ZIM145878-01','20121201','20131130',NULL)

;With Islands as (
    select ID,FromDate,ToDate from @t t1 where not exists (select * from @t t2 where t2.ToDate = DATEADD(day,-1,t1.FromDate) and t1.ID = t2.ID)
    union all
    select i.ID,i.FromDate,t.ToDate
    from
        Islands i
            inner join
        @t t
            on
                i.ID = t.ID and
                i.ToDate = DATEADD(day,-1,t.FromDate)
)
select ID,FromDate,MAX(ToDate) from Islands
group by ID,FromDate

Results:

ID                                                 FromDate   
-------------------------------------------------- ---------- ----------
ZIM145878-01                                       2010-05-15 2011-05-14
ZIM145876-01                                       2011-03-01 2014-02-28
ZIM145878-01                                       2011-12-01 2013-11-30
ZIM145881-02                                       2012-02-01 2013-06-30

It could be more efficient if the occupancy was recorded as a semi-open interval (e.g. Inclusive FromDate, Exclusive ToDate) - because then we wouldn't have to call DATEADD to find the place where the periods join together.

I don't know what the Diff column is about, and I haven't used it.

Upvotes: 1

Related Questions