-1

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Curti7
  • 1
  • 2
  • 1
    *"I have already tried various versions of partition"* Include those attempts in your question, so that we can see what it is you've tried, and help you correct it so it works. – Thom A Jun 24 '22 at 09:28
  • 1
    Also, this looks like a gaps and island issue; a search of this methodology should give you a wealth of resources. – Thom A Jun 24 '22 at 09:29

1 Answers1

1

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];
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thank you so much. To me the buzzword "gaps and island problem" is new, so that was already a great hint! Now, I have tried to apply your code, however now I just get a single row for each monthID: From | To | Value 202211 | 202211 | 10 202212 | 202212 | 10 and so on.. – Curti7 Jun 24 '22 at 09:58
  • I don't follow what you mean by *"I just get a single row for each monthID"*, @Curti7 . The answer, above, returns the results you want: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e5f7708fa444996cdeb9fead38b3efe5) – Thom A Jun 24 '22 at 10:00
  • My fault, I missinterpreted the "minus" (-) - now it works perfectly. Thank you very much :-) – Curti7 Jun 24 '22 at 10:13