How to generate the sequence number based on condition in sql server

2
votes

I need to display a New Sequence column value based on the Score 1 or 0.

MonthlyDate Score  New Sequence
----------- ------ ----------
2019-08-01   1      0
2019-08-02   0      1
2019-08-03   0      2
2019-08-04   0      3
2019-08-05   1      0
2019-08-06   0      1
2019-08-07   0      2
2019-08-08   0      3

I am trying to achieve on of the calculation in my project based on the new sequence value.

I have tried sum(score) over (Order By Date rows unbounded preceding)

Case when Score = 1 
 THEN 0
 ELSE (CASE WHEN LAG(Score) OVER (ORDER BY MonthlyDate) = 0 
 THEN 1
 ELSE (SUM(Score) over (Order by MonthlyDate rows unbounded preceding)) 
 END) 
END as NewSequence
1
sqlsql-server
Why did you try SUM(SCORE)? Score is 0. The SUM of a bunch of zeros will be zero. - Tab Alleman
that's my worst part, I wouldn't do that. please look on my actual ask. - Thiru
So new sequence resets to 0 when score=1? - Jamiec
@Jamiec, The answer is Yes. - Thiru

1 Answers

3
votes

I use mysql because the online tool but very similar in sql server.

SQL DEMO

with cte as (
    SELECT *, SUM(Score) OVER (ORDER BY MonthlyDate) as grp
    FROM scores
)
SELECT *, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY MonthlyDate) as new_seq
FROM cte

OUTPUT

| MonthlyDate         | Score | New_Sequence | grp | new_seq |
| ------------------- | ----- | ------------ | --- | ------- |
| 2019-08-01 00:00:00 | 1     | 0            | 1   | 1       |
| 2019-08-02 00:00:00 | 0     | 1            | 1   | 2       |
| 2019-08-03 00:00:00 | 0     | 2            | 1   | 3       |
| 2019-08-04 00:00:00 | 0     | 3            | 1   | 4       |
| 2019-08-05 00:00:00 | 1     | 0            | 2   | 1       |
| 2019-08-06 00:00:00 | 0     | 1            | 2   | 2       |
| 2019-08-07 00:00:00 | 0     | 2            | 2   | 3       |
| 2019-08-08 00:00:00 | 0     | 3            | 2   | 4       |