Geek Answers Handbook

SQL Server 2012: Conditional increase in user counter ROW_NUMBER ()

I am trying to apply ROW_NUMBER()to increase the counter based on specific conditions.

My data looks like this: goal counter - column Prep

    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 column PrepIndicatorfor now, the business logic I'm trying to implement is as follows:

  • For each of the Ids, starting at 1, increment the counter Prepif the DSR is less than 42.
  • If it is 42 or more, reset the Prep counter to 1.

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

I would rather achieve this without a column PrepIndicator, if possible.

ROW_NUMBER()?

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

, DSR >= 42.

. !

+4
2

. " " , . IDENTITY ROW_NUMBER() OVER ORDER BY(/* your logic here */). (. 1680648, 14, 2), , - .

, , , . ROW_NUMBER(), . , , .

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

"", -, 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
0

-, , SQL ; IdK. , :

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

, , , /. id PrepIndicator 5 :

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

, DSR = 60 . , . Select (*)..., 5 :

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

, .

0

More articles:


All Articles