CODEWITHSUNDEEP
sqlsql-serverwindow-functions
DooDoo
DooDoo

Reputation: 13487

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

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

Upvotes: 0

Views: 107

Answers (3)

Ragnar
Ragnar

Reputation: 227

WITH GroupedData
AS (SELECT F1,F2,YEAR,MONTH, 
           CASE
               WHEN F2 != LAG(F2, 1, F2) OVER (PARTITION BY F1 ORDER BY YEAR, MONTH) THEN
                   1
               ELSE
                   0
           END AS IsNewGroup
    FROM tbl),
     Groups
AS (SELECT F1,F2,YEAR,MONTH, 
           SUM(IsNewGroup) OVER (PARTITION BY F1 ORDER BY YEAR, MONTH) AS GroupID
    FROM GroupedData)
SELECT F1,F2,YEAR,MONTH, 
       ROW_NUMBER() OVER (PARTITION BY F1, GroupID ORDER BY YEAR, MONTH) AS SEQUENCE
FROM Groups
ORDER BY F1,YEAR,MONTH;

We use the LAG() function to look at the F2 value of the previous row (within the same F1 group, ordered by Year and Month). If the F2 value is different from the previous row, it means a new group is starting. In that case, we mark it with 1 (called IsNewGroup). If it's the same, we mark it with 0.

We take the IsNewGroup column and calculate a running total using SUM(). This running total (called GroupID) helps us assign a unique number to each group of rows that share the same F2 value within an F1 group. For each group (based on F1 and GroupID), we generate a sequence number using ROW_NUMBER(). This sequence is ordered by Year and Month. Finally, we sort everything by F1, Year, and Month so the results appear in the correct order.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

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

Upvotes: 3

Guillaume Outters
Guillaume Outters

Reputation: 1931

After having determined the borders (moments in the F1-grouped sequences where F2 changes), you can subtract the position of the last border from all subsequent positions:

With bordered as
(
    Select 
        *,
        Row_number() over win as pos,
        case when F2 = lag(F2) over win then null else Count(1) over win - 1 end as ignore_before
    From 
        @tbl
    Window win As (partition by F1 order by year, month)
)
Select F1, F2, year, month, pos - max(ignore_before) over win Sequence
from bordered
Window win As (partition by F1 order by year, month)
Order by
    f1, year, month;

Here it runs in an SQLFiddle.

As you'll want the windowed max of a windowed position,
you'll need at least one pass for each window function,
thus the necessary use of a CTE.

Note that I used a window definition to avoid repeating its use (and so, avoid copy errors).

Upvotes: 1

Related Questions