CODEWITHSUNDEEP
sql-servert-sqlsequence
Nils
Nils

Reputation: 514

Sequence number start from set value

i would like to add a sequence nr from a set value.

is this output possible? 

p_id    date          days     OUTPUT(what i want)
3385    2012-02-02    556      0
3385    2012-02-03     1       1
3385    2012-09-24     234     0
3385    2012-09-25     1       1
3385    2013-11-12     413     0
3385    2013-11-13     1       1
3385    2013-11-14     1       2
3385    2013-11-15     1       3
3385    2014-09-09     298     0
3385    2014-09-10     1       1
3385    2014-09-11     1       2
3385    2015-11-11     426     0
3385    2015-11-12     1       1
3385    2015-11-13     1       2
3385    2015-11-14     1       3
3385    2015-11-15     1       4
3385    2015-11-16     1       5
3385    2015-11-17     1       6
3385    2015-11-18     1       7
3385    2015-11-19     1       8
3385    2015-11-20     1       9

The days column counts days from the row abow it.

if the days value is greater the 5 then start a new "0" and then a new sequence number.

Upvotes: 3

Views: 192

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT p_id, [date], days,
       ROW_NUMBER() OVER (PARTITION BY p_id, grp 
                          ORDER BY [date]) - 1 AS [OUTPUT]
FROM (
  SELECT p_id, [date], days, 
         SUM(IIF(days > 5, 1, 0)) OVER (PARTITION BY p_id
                                        ORDER BY [date]) AS grp
  FROM mytable ) AS t

Explanation:

The inner query uses SUM() OVER() to produce the following output:

p_id    date        days    grp
===============================
3385    2012-02-02  556     1
3385    2012-02-03  1       1
3385    2012-09-24  234     2
3385    2012-09-25  1       2
3385    2013-11-12  413     3
3385    2013-11-13  1       3
3385    2013-11-14  1       3
3385    2013-11-15  1       3
3385    2014-09-09  298     4
3385    2014-09-10  1       4
3385    2014-09-11  1       4
3385    2015-11-11  426     5
3385    2015-11-12  1       5
3385    2015-11-13  1       5
3385    2015-11-14  1       5
3385    2015-11-15  1       5
3385    2015-11-16  1       5
3385    2015-11-17  1       5
3385    2015-11-18  1       5
3385    2015-11-19  1       5
3385    2015-11-20  1       5

grp field is essentially the running total of 'greater than 5' occurrences. Using this field in an outer query we can easily produce the required enumeration with ROW_NUMBER window function.

Upvotes: 5

Related Questions