CODEWITHSUNDEEP
sqlsql-server
Cur123
Cur123

Reputation: 89

Group by ALL columns corresponding to consecutive dates SQL Server

I am working in SQL Server 2012 and have a dataset like this:

ID    DATE1         TMT
-----------------------
121   2016-04-01     B
121   2016-04-04     A
121   2016-04-06     A
121   2016-04-08     A
121   2016-04-11     B
121   2016-04-13     B
121   2016-04-15     A
122   2016-03-14     A
122   2016-03-16     A
122   2016-03-18     B
122   2016-03-21     B
122   2016-03-24     A
122   2016-03-29     A

Desired output:

ID    BEGIN_DATE    END_DATE     TMT  DAY_COUNT
-----------------------------------------------
121   2016-04-01    2016-04-01    B     1
121   2016-04-04    2016-04-08    A     4
121   2016-04-11    2016-04-13    B     2
121   2016-04-15    2016-04-15    A     1
122   2016-03-14    2016-03-16    A     2
122   2016-03-18    2016-03-21    B     3
122   2016-03-24    2016-03-29    A     5

Code I have now:

;WITH S AS 
(
     SELECT 
         ID, MIN(DATE1) BEGIN_DATE, MAX(DATE1) END_DATE, TMT
     FROM 
         MyTable
     GROUP BY 
         id, TMT
)
SELECT 
    *, 
    CASE WHEN DATEDIFF(d, BEGIN_DATE, END_DATE) = 0 
            THEN 1 
            ELSE DATEDIFF(d, BEGIN_DATE, END_DATE) 
    END DAY_COUNT 
FROM 
    S

Code produces result like this:

ID    BEGIN_DATE    END_DATE     TMT  DAY_COUNT
------------------------------------------------
121   2016-04-01    2016-04-13    B     12
121   2016-04-04    2016-04-15    A     11
122   2016-03-14    2016-03-29    A     15
122   2016-03-18    2016-03-21    B     3

Not sure how to get from here to desired output. Any help would be appreciated!

Thank you!

Upvotes: 1

Views: 62

Answers (2)

Cur123
Cur123

Reputation: 89

@ZLK 's answer worked perfect for me! Thank you @ZLK! Here is the code, if anyone else needs it-

select id, min(date1), max(date1), tmt, datediff(day, min(date1), max(date1)) 
from (select *, rn = row_number() over (order by id, date1) - row_number() 
over (partition by tmt order by id, date1) from mytable) as t group by id, 
 tmt, rn;

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

It is a gap and island problem try it like this

select t.id, 
       min(date1) as begin_date, 
       max(date1) as end_date, 
       datediff(day, min(date1), max(date1)) day_count,
       t.TMT
from
(
    select *, row_number() over (partition by id, TMT order by date1) - 
              row_number() over (partition by id order by date1) grn
    from s
) t
group by t.id, t.TMT, grn

The crucial is the nested subquery where there are two row_number() functions in order to isolate consecutive occurrence of the TMT per id. Once you have the grn value then the rest is simple group by.

Upvotes: 1

Related Questions