Using SQL Server window functions with year and month(Period of time)

Question

Please consider this script:

Declare @tbl Table
(
    F1    int,
    F2    int,
    Year  int,
    Month tinyint
)

Insert into @tbl 
values
(10, 1, 2020, 1),
(10, 1, 2020, 2),
(10, 1, 2020, 3),
(10, 1, 2020, 4),
(10, 2, 2020, 5),
(10, 1, 2020, 6),
(10, 1, 2020, 7),
(11, 1, 2020, 1),
(11, 1, 2020, 2),
(11, 2, 2020, 3),
(11, 2, 2020, 4),
(11, 1, 2020, 5),
(12, 1, 2020, 1),
(12, 1, 2020, 2),
(12, 1, 2020, 3),
(12, 1, 2020, 4)

I wrote this query:

Select 
    F1, F2, year, month,
    Row_number() over (partition by F1, F2 order by year, month) as rownumber,
    Rank() over (partition by F1, F2 order by year, month) as rnk
From 
    @tbl
Order by 
    f1, f2, year, month

This query returns this result set:

F1	F2	Year	Month	Rownumber	Rnk
10	1	2020	1	1	1
10	1	2020	2	2	2
10	1	2020	3	3	3
10	1	2020	4	4	4
10	1	2020	6	5	5
10	1	2020	7	6	6
10	2	2020	5	1	1
11	1	2020	1	1	1
11	1	2020	2	2	2
11	1	2020	5	3	3
11	2	2020	3	1	1
11	2	2020	4	2	2
12	1	2020	1	1	1
12	1	2020	2	2	2
12	1	2020	3	3	3

But I want this result:

F1	F2	Year	Month	Sequence
10	1	2020	1	1
10	1	2020	2	2
10	1	2020	3	3
10	1	2020	4	4
10	2	2020	5	1
10	1	2020	6	1
10	1	2020	7	2
11	1	2020	1	1
11	1	2020	2	2
11	2	2020	3	1
11	2	2020	4	2
11	1	2020	5	1
12	1	2020	1	1
12	1	2020	2	2
12	1	2020	3	3
12	1	2020	4	4

That is, in each set of the same F1 value,
sequence by year then month,
but reset the year month sequence number at each F2 change.

How can I achieve my desired result? Thanks

Answer 1

This is basically a gaps and islands problems in disguise. Here, each "island" is a group of records belonging to the same F1 block where the F2 value has not changed in sequence across year and month. We can generate a pseudo-group to keep track of these records, and then take the ROW_NUMBER() to generate the final sequence values. Note that this pseudo-group value resets to 1 when either the F1 block ends or the F2 values changes.

WITH cte AS (
    SELECT *, CASE WHEN LAG(F2) OVER (PARTITION BY F1 ORDER BY Year, Month) != F2 THEN 1 ELSE 0 END AS flag
    FROM @tbl
),
cte2 AS (
    SELECT *, SUM(flag) OVER (PARTITION BY F1 ORDER BY Year, Month) grp
    FROM cte
)

SELECT F1, F2, Year, Month,
       ROW_NUMBER() OVER (PARTITION BY F1, grp ORDER BY Year, Month) AS seq
FROM cte2
ORDER BY F1, Year, Month;

Demo