SQL Server Looping - Submission Consistency

ruarlubt 于 2023-02-21 发布在其他

关注(0)|答案(1)|浏览(67)

I have a table structured like this:
| SchoolId | YearTerm | Submitted |
| ------------ | ------------ | ------------ |
| 1 | 2022-1 | 1 |
| 1 | 2022-2 | 1 |
| 1 | 2022-3 | 1 |
| 1 | 2022-4 | 1 |
| 2 | 2022-1 | 1 |
| 2 | 2022-2 | 1 |
| 2 | 2022-4 | 1 |

I am trying to figure out how many YearTerms these schools have been submitting in a row so I wrote some logic that gives me this output:

SchoolId	YearTerm	Submitted	SubmitOrder
1	2022-1	1	1
1	2022-2	1	2
1	2022-3	1	3
1	2022-4	1	4
2	2022-1	1	1
2	2022-2	1	2
2	2022-4	1	3

SELECT
G.*
,ROW_NUMBER() OVER( PARTITION BY SchoolId ORDER BY G.YearTerm ASC) SubmitOrder
FROM
(
    SELECT

    DISTINCT
    SchoolId
    ,YearTerm 
    ,1 Submitted
    FROM Table 
) G

The problem is that for schools that skip a YearTerm (in the picture above, SchoolId = 2 is getting credit for 3 consecutive submissions when in reality it should only be 2). The desired output I am looking for is:

SchoolId	YearTerm	Submitted	SubmitOrder
1	2022-1	1	1
1	2022-2	1	2
1	2022-3	1	3
1	2022-4	1	4
2	2022-1	1	1
2	2022-2	1	2
2	2022-4	1	1

Because SchoolId = 2 did not submit in 2022-3, there submission in 2022-4 is now there first submission in chronological order.

Any help is much appreciated!

Write a loop to solve for submissions consistency.

sql-server

来源：https://stackoverflow.com/questions/75513657/looping-submission-consistency

1条答案

按热度按时间

y1aodyip1#

Something like this perhaps?

SELECT  SchoolID, YearTerm, Submitted
,   ROW_NUMBER() OVER(PARTITION BY SchoolID, Streak ORDER BY YearTerm) AS submissionStreakID
FROM    (
    SELECT  SUM(CASE WHEN RIGHT(YearTerm, 1) = '1' 
            THEN CASE WHEN prevTerm <> CONCAT((CAST(LEFT(YearTerm, 4) AS INT) - 1), '-1') THEN 1 ELSE 0 END
            ELSE CASE WHEN prevTerm <> CONCAT(LEFT(YearTerm, 5), CAST(RIGHT(YearTerm, 1) AS INT) - 1) THEN 1 ELSE 0 END
        END) OVER(PARTITION BY SchoolID ORDER BY YearTerm) AS streak
    ,   *
    FROM    (
        SELECT  *
        ,   LAG(YearTerm) OVER(PARTITION BY SchoolID ORDER BY YearTerm) AS prevTerm
        FROM
        (
            VALUES  (1, N'2022-1', 1)
            ,   (1, N'2022-2', 1)
            ,   (1, N'2022-3', 1)
            ,   (1, N'2022-4', 1)
            ,   (1, N'2023-2', 1)
            ,   (1, N'2023-3', 1)
            ,   (1, N'2024-1', 0)
            ,   (2, N'2022-1', 1)
            ,   (2, N'2022-2', 1)
            ,   (2, N'2022-4', 1)
        ) t (SchoolId,YearTerm,Submitted)
        WHERE   Submitted = 1
        ) x
    ) x

I check if current term follows the previous and if not, increments the "streak" group count. Then it's an easy ROW_NUMBER() that calculates where in the streak you are

赞(0）回复(0）举报 2023-02-21

我来回答

SQL Server Looping - Submission Consistency

1条答案

相关问题

热门标签

最新问答