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

Question

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

Why did you try SUM(SCORE)? Score is 0. The SUM of a bunch of zeros will be zero. — Tab Alleman
– Tab Alleman, Commented Aug 30, 2019 at 15:14
that's my worst part, I wouldn't do that. please look on my actual ask. — Thiru
– Thiru, Commented Aug 30, 2019 at 15:17

David Browne - Microsoft · Accepted Answer · 2019-08-30 15:43:28Z

3

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       |

edited Aug 30, 2019 at 15:43

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

answered Aug 30, 2019 at 15:21

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Juan Carlos Oropeza Over a year ago

realize my seq start in 1, you can do -1 + row_number()

Thiru Over a year ago

I want new sequence should be reset to 0 when score is 1

Juan Carlos Oropeza Over a year ago

did you saw my previous comment? do you really need I update my answer for that?

Thiru Over a year ago

Thank you, It works perfectly fine. if you want to update the answer that would be great. thanks again!

Juan Carlos Oropeza Over a year ago

@scsimon I know but doesnt have Text to DDL :(

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related