Trying to find an efficient way to create the column C values which is based on column B and increments based on B being 1 or -1 and the count restarts when B switches between B being 1 or -1.
CREATE TABLE #T (a int, b int, c int);
INSERT INTO #T VALUES (1, 1, 1),
(2, 1, 2),
(3, -1, -1),
(4, -1, -2),
(5, -1, -3),
(6, -1, -4),
(7, 1, 1),
(8, 1, 2);
Thank you!
The trick is to group column b. You can do this with the base code taken from this answer. Now you need to switch the incrementer to be positive or negative. Easiest way to do that is to multiply the value by column b. For example:
SELECT
a,b,
ROW_NUMBER() OVER (PARTITION BY b, grp ORDER BY a) * b as c
FROM (SELECT T.*,
(ROW_NUMBER() OVER (ORDER BY a) -
ROW_NUMBER() OVER (PARTITION BY b ORDER BY a)
) as grp
FROM T) T
ORDER BY a
