TSQL - GROUP BY on continous rows only-CodePudding

I am working on a SQL Server 2017 (v14.0).

I have a table like this:

Key | State    | from       | until      |
---- ---------- ------------ ------------ 
100 | open     | 01.01.2021 | 01.01.2021 |
100 | open     | 02.01.2021 | 02.01.2021 |
100 | closed   | 03.01.2021 | 13.01.2021 |
100 | open     | 14.01.2021 | 20.01.2021 |
100 | open     | 20.01.2021 | 30.01.2021 |

I want to group it by Key and State, but only for continuous rows.

So my expected result would be something like:

Key | State    | from       | until      |
---- ---------- ------------ ------------ 
100 | open     | 01.01.2021 | 02.01.2021 |
100 | closed   | 03.01.2021 | 13.01.2021 |
100 | open     | 14.01.2021 | 30.01.2021 |

Any idea on how to do this? I have the strong feeling, that this should be possible with the help of ROW_NUMBER somehow, but I was not able to figure it out yet...

(In this example data some weird group by calendarweek or something similar might be possible, but this is not my intention)

CodePudding user response：

It is a Gaps and Islands problem. One solution is this:

WITH cte1 AS (
    SELECT *, CASE WHEN LAG([state]) OVER (PARTITION BY [key] ORDER BY [from]) = [state] THEN 0 ELSE 1 END AS chg
    FROM t
), cte2 AS (
    SELECT *, SUM(chg) OVER (PARTITION BY [key] ORDER BY [from]) AS grp
    FROM cte1
)
SELECT [key], grp, MIN([state]), MIN([from]), MAX([until])
FROM cte2
GROUP BY [key], grp
ORDER BY [key], grp

CodePudding user response：

One possibility is as below added:

Create table myTable_o1
 (
 [key] int
 ,[state] varchar(100)
 ,[From] date
 ,[Until] date
 )

insert into myTable_o1 values (100, 'open', '2021-01-01','2021-01-01')
insert into myTable_o1 values (100, 'open', '2021-01-02','2021-01-02')
insert into myTable_o1 values (100, 'closed', '2021-01-03','2021-01-13')
insert into myTable_o1 values (100, 'open', '2021-01-4','2021-01-20')
insert into myTable_o1 values (100, 'open', '2021-01-20','2021-01-30')

SELECT 
   [key] 
   ,[state] 
   ,[From] 
   ,[until] 
FROM
(
Select 
  [key] 
 , [state] 
 , [From] 
 , row_number() over (partition by tiles order by [key]) row_num
 , ISNULL(Lead(Until) over (partition by tiles order by [key]) , Until) [until]
 FROM
 (
    SELECT * ,
    Ntile(2) over ( order by [Key]) as [tiles]
    from myTable_o1
   ) AS A
 ) AS B WHERE B.row_num in (1,3)