CODEWITHSUNDEEP
sqlsql-servert-sql
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

Resetting a Count in SQL

I have data that looks like this:

ID     num_of_days
1      0
2      0
2      8
2      9
2      10
2      15
3      10
3      20

I want to add another column that increments in value only if the num_of_days column is divisible by 5 or the ID number increases so my end result would look like this:

ID     num_of_days    row_num
1      0                    1
2      0                    2
2      8                    2
2      9                    2
2      10                   3
2      15                   4
3      10                   5
3      20                   6

Any suggestions?

Edit #1:

num_of_days represents the number of days since the customer last saw a doctor between 1 visit and the next.

A customer can see a doctor 1 time or they can see a doctor multiple times. If it's the first time visiting, the num_of_days = 0.

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270311

SQL tables represent unordered sets. Based on your question, I'll assume that the combination of id/num_of_days provides the ordering.

You can use a cumulative sum . . . with lag():

select t.*,
       sum(case when prev_id = id and num_of_days % 5 <> 0
                then 0 else 1
           end) over (order by id, num_of_days)
from (select t.*,
             lag(id) over (order by id, num_of_days) as prev_id
      from t
     ) t;

Here is a db<>fiddle.

If you have a different ordering column, then just use that in the order by clauses.

Upvotes: 2

Related Questions