Query and Partition By clause group by window

Question

I've the following code

declare @test table (id int, [Status] int, [Date] date)

insert into @test (Id,[Status],[Date]) VALUES
    (1,1,'2018-01-01'),
    (2,1,'2018-01-01'),
    (1,1,'2017-11-01'),
    (1,2,'2017-10-01'),
    (1,1,'2017-09-01'),
    (2,2,'2017-01-01'),
    (1,1,'2017-08-01'),
    (1,1,'2017-07-01'),
    (1,1,'2017-06-01'),
    (1,2,'2017-05-01'),
    (1,1,'2017-04-01'),
    (1,1,'2017-03-01'),
    (1,1,'2017-01-01')

SELECT
    id,
    [Status],
MIN([Date]) OVER (PARTITION BY id,[Status] ORDER BY [Date],id,[Status] ) as WindowStart,
max([Date]) OVER (PARTITION BY id,[Status] ORDER BY [Date],id,[Status]) as WindowEnd,
COUNT(*) OVER (PARTITION BY id,[Status] ORDER BY [Date],id,[Status] ) as total
from @test

But the result is this:

id  Status  WindowStart WindowEnd   total
1   1   2017-01-01  2017-01-01  1
1   1   2017-01-01  2017-03-01  2
1   1   2017-01-01  2017-04-01  3
1   1   2017-01-01  2017-06-01  4
1   1   2017-01-01  2017-07-01  5
1   1   2017-01-01  2017-08-01  6
1   1   2017-01-01  2017-09-01  7
1   1   2017-01-01  2017-11-01  8
1   1   2017-01-01  2018-01-01  9
1   2   2017-05-01  2017-05-01  1
1   2   2017-05-01  2017-10-01  2
2   1   2018-01-01  2018-01-01  1
2   2   2017-01-01  2017-01-01  1

And I need to be grouped by window like this.

id  Status  WindowStart WindowEnd   total
1   1   2017-01-01  2017-04-01  3
1   2   2017-05-01  2017-05-01  1
1   1   2017-06-01  2017-09-01  4
1   2   2017-10-01  2017-10-01  1
1   1   2017-11-01  2018-01-01  2
2   1   2018-01-01  2018-01-01  1
2   2   2017-01-01  2017-01-01  1

The first group for the id= 1 Status = 1 should end at the first row with Status = 2 (2017-05-01) so the total is 3 and then start again from the 2017-06-01 to 2017-09-01 with a total of 4 rows.

How can get this done?

score 1 · Answer 1 · answered Jan 18 '18 at 15:55

This is a "classic" Groups and Island issue. There's probably 1000's of answers for these on the Internet.

This works for what you're after, however, try having a bit more of a research before hand. :)

WITH Groups AS(
    SELECT t.*,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY [Date]) - 
           ROW_NUMBER() OVER (PARTITION BY id, [status] ORDER BY [Date]) AS Grp
    FROM @test t)
SELECT G.id,
       G.[Status],
       MIN([Date]) AS WindowStart,
       MAX([date]) AS WindowsEnd,
       COUNT(*) AS Total
FROM Groups G
GROUP BY G.id,
         G.[Status],
         G.Grp
ORDER BY G.id, WindowStart;

Note, that the ordering of your last 2 lines is the other way round in this solution; it seems you're ordering ASCENDING for id 1, for DESCENDING for id 2 in your expected results.

score 0 · Accepted Answer · answered Jan 18 '18 at 15:55

Here is one way using LAG function

;WITH cte
     AS (SELECT *,
                grp = Sum(CASE WHEN prev_val = Status THEN 0 ELSE 1 END)
                        OVER(partition BY id ORDER BY Date)
         FROM   (SELECT *,
                        prev_val = Lag(Status)OVER(partition BY id ORDER BY Date)
                 FROM   @test) a)
SELECT id,
       Status,
       WindowStart = Min(date),
       WindowEnd = Max(date),
       Total = Count(*)
FROM   cte
GROUP  BY id, Status, grp

Using lag function first find the previous status of each date, then using Sum over() create a group by incrementing the number only when there is a change in status.

Query and Partition By clause group by window

2 Answers2