sql to assign an incrementing number based on another column value

Question

I have a sql problem that I would like some assistance with. Please see the image below:

So, it is the last column (newflag) that I want implemented in a sql. The idea is to increment by 1 when the value in 'Breakpoint' changes. Starting value is 1. When the value repeats in 'Breakpoint', do not increment but keep previous value.

I want to do this for every unique client and location.

Thank you very much for your assistance

You can use lead/lag for this. As explained in the [question guide](https://stackoverflow.com/help/how-to-ask), help us help you and edit your question to include a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) with sample data and desired results as *consumable text*, not [images](https://meta.stackoverflow.com/questions/285551) of data. — Stu, May 12 '23 at 17:30

score 1 · Answer 1 · answered May 12 '23 at 18:42

1

You can do:

select x.*,
  sum(i) over(partition by clientid, locationid
      order by visitdate) + 1 as newflag
from (
  select t.*,
    case when breakpoint <> lag(breakpoint)
      over(partition by clientid, locationid order by visitdate) 
    then 1 else 0 end as i
  from t
) x

answered May 12 '23 at 18:42

The Impaler

45,731
9
39
76

Hi, the code is partially working. Right now, if the splitflag changes from 0 to 1, add 1. This is needed, but when the next splitflag is 1, I still want to increment by 1 – JGF May 19 '23 at 18:50

sql to assign an incrementing number based on another column value

1 Answers1