T-SQL create time windows (from/to) with alternating values-CodePudding

I need to transform rows on a monthly basis to rows with a time window based on alternating values. I have already tried various versions of partition, dense_rank and joins with row_number on row_number-1 but I somehow cannot get to the right result.

Here's my data:

month_ID	Value
202211	10
202212	10
202301	10
202302	12
202303	12
202304	10
202305	10
202306	12
202307	12

Here's what I want my output to be:

From	To	Value
202211	202301	10
202302	202303	12
202304	202305	10
202306	202307	12

The time frames can differ.

CodePudding user response：

As I mentioned in the comments, this is a gaps and island problem. One method is to use a couple of ROW_NUMBERs to create the groups, and then you can get the MIN and MAX for each group:

WITH CTE AS(
    SELECT MonthID,
           [Value],
           ROW_NUMBER() OVER (ORDER BY MonthID ASC) - 
           ROW_NUMBER() OVER (PARTITION BY [Value] ORDER BY MonthID ASC) AS Grp
    FROM (VALUES(202211,10),
                (202212,10),
                (202301,10),
                (202302,12),
                (202303,12),
                (202304,10),
                (202305,10),
                (202306,12),
                (202307,12))V(MonthID, [Value]))
SELECT MIN(MonthID) AS [From],
       MAX(MonthID) AS [To],
       [Value]
FROM CTE
GROUP BY Grp,
         [Value]
ORDER BY [From];