SQL Server 2012: Conditionally Incrementing a counter user ROW_NUMBER()

Question

I am trying to apply ROW_NUMBER() to increment a counter based on particular conditions.

My data looks like this, with the target counter being the Prep column

    id       DSR    PrepIndicator   Prep
   --------------------------------------
    1662835  -1     1               1
    1662835  14     2               2
    1662835  14     2               3
    1662835  20     2               4
    1667321  -1     1               1
    1667321  30     2               2
    1667321  14     2               3
    1680648  -1     1               1
    1680648  14     2               2
    1680648  60     1               1
    1680648  14     2               2
    1680648  14     2               3
    1683870  -1     1               1
    1683870  12     2               2
    1683870  10     2               3
    1683870  60     1               1
    1683870  7      2               2

Ignoring the PrepIndicator column for the moment, the business logic I am trying to implement is as follows:

• For each of the Id's, starting from 1, increment the Prep counter if the DSR is less than 42.
• If it is 42 or greater, reset the Prep counter to 1.

The PrepIndicator, in effect, creates a flag to implement this, in that if PrepIndicator = 1 then Prep = 1. If PrepIndicator = 2, then increment Prep.

I'd prefer to achieve this without the PrepIndicator column if possible.

How would I achieve this conditional increment with ROW_NUMBER()?

I've tried

ROW_NUMBER() OVER (PARTITION BY id, PrepIndicator ORDER BY id) 

but it doesn't seem to work when the DSR is >= 42.

Any suggestions or help would be great. Thanks!

Answer 1

First, you will need explicit ordering. "Incrementing the counter" only has meaning if you have a previous value. You can add an IDENTITY column to the table, or use ROW_NUMBER() OVER ORDER BY(/* your logic here */). In your table, you do not even have unique values for the first three columns (see 1680648, 14, 2), so I would think adding an ID is the way to go.

To do what you want to achieve, I believe you must do this in a loop. If you use ROW_NUMBER() you may wish to select into a temporary table. By the nature of your question, the term counter indicates you will have a variable.

UPDATE TableA SET rowId = ROW_NUMBER() OVER(ORDER BY id, DSR, PrepIndicator)

then "conditional" seems to signal a good use of CASE

DECLARE @counter INT = 1
DECLARE @row INT = 1
DECLARE @DSR INT

UPDATE TableA SET Prep = @counter
SET @row = (SELECT rowId FROM TableA WHERE rowId > @row)

WHILE EXISTS( SELECT TOP 1 1 FROM TableA WHERE rowId = @row )
BEGIN
    SELECT @DSR = DSR FROM TableA WHERE rowId = @row
    SET @counter = CASE WHEN @DSR < 42 THEN @counter + 1 ELSE 1 END
    UPDATE TableA SET Prep = @counter WHERE rowId = @row
    SET @row = (SELECT rowId FROM TableA WHERE rowId > @row)
END

Answer 2

First, you need to add a primary key because there is no physical order in a SQL table; we can call it IdK. The following code should then give you what you want:

select *, row_number() over (partition by Id, (Select Count (*) from MyTable t2 where t2.idk <= t1.idk and t2.id = t1.id and DSR >= 42) order by idk) prep
from MyTable t1
order by idk

As to why your code doesn't work, this is because the rows are first grouped before the partition/numbering is done. In the case with the two columns id and PrepIndicator for the partition, we get the following intermediary result for the last 5 row before the numbering:

id       DSR    PrepIndicator   Row_Number (Id, PrepIndicator)
1683870  -1     1               1
1683870  60     1               2
1683870  12     2               1
1683870  10     2               2
1683870  7      2               3

Notice that the line with DSR = 60 is now in the second position. This is clearly what you don't want to have. In the case with the Select count(*)..., we have the following result for the last 5 rows after the grouping is done, just before the numbering:

id       DSR    ...Count()   Row_Number (Id, ...Count())
1683870  -1     0               1
1683870  12     0               2
1683870  10     0               3
1683870  60     1               1
1683870  7      1               2

You can notice that in this case, there is no change of position for any row.