CODEWITHSUNDEEP
sqlsql-server
Ronak Shah
Ronak Shah

Reputation: 1549

How to increment new column value conditionally

I want to generate new column with conditionality incremented value.

Lets say I have demo data like below:

SubjID  dstart  dstop   primarykey
1       1       4       1
1       5       15      2
1       16      25      3
1       26      35      4
2       1       4       5
2       5       15      6
2       16      25      7
2       26      35      8

Now I want Result like below:

SubjID  dstart  dstop   primarykey  NewCol
1       1       4       1            1
1       5       15      2            1
1       16      25      3            1
1       26      35      4            1
2       36      40      5            2
2       5       15      6            3
2       16      25      7            3
2       26      35      8            3

So I want to generate New column with below condition:

Only Increment new column rank if any of below condition false

1) Difference between current row's dstart value and previous row's dstop value is not 1

2) Current row's Subject id is different than previous row's subjected

Check NewcCol values in My expected output which need to be generated by your query.

Upvotes: 1

Views: 248

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do what you want with window functions. The following version uses cumulative sum, which is available in SQL Server 2012+;

select SubjID, dstart, dstop, primarykey,
       sum(case when dstart <> prevdstop + 1 or prevSubjId is null then 1 else 0 end) over
           (order by primarykey) as newcol
from (select t.*,
             lag(dstop) over (partition by subjId order by primarykey) as prevdstop,
             lag(subjId) over (partition by subjId order by primarykey) as prevSubjId
      from table t;

The idea is to flag row where the new column should change values. Then, just do a cumulative sum of those flags.

Upvotes: 2

Related Questions