First of all, please read my comment to the question...
In my opinion, there's few ways to achieve that.
One of them is to create custom row numbering using
CTE[
^].
Another one is to use
ROW_NUMBER()
function:
DECLARE @tmp TABLE ([User] VARCHAR(10), [Month] INT, Lmonth INT, ARR INT)
INSERT INTO @tmp ([User], [Month], [Lmonth], ARR)
VALUES('A1',200810, 200902, 1),
('A1',200811, 200902, 1),
('A1',200812, 200902, 2),
('A1',200901, 200902, 3),
('A1',200902, 200902, 3),
('A1',200903, 200902, 4),
('A1',200904, 200902, 4),
('A1',200905, 200902, 0),
('A1',200906, 200902, 1),
('A1',200907, 200902, 2),
('B1',200905, 200908, 0),
('B1',200906, 200908, 1),
('B1',200907, 200908, 2),
('B1',200908, 200908, 2),
('B1',200909, 200908, 2),
('B1',200910, 200908, 0),
('B1',200911, 200908, 1),
('B1',200912, 200908, 1)
SELECT [User], [Month], [Lmonth], ARR, 0 AS RN
FROM @tmp
WHERE [Month]<[Lmonth] OR ARR = 0
UNION ALL
SELECT [User], [Month], [Lmonth], ARR, ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY [Month]) AS RN
FROM @tmp
WHERE [Month]>=[Lmonth] AND ARR > 0
Result:
User Month Lmonth ARR RN
A1 200810 200902 1 0
A1 200811 200902 1 0
A1 200812 200902 2 0
A1 200901 200902 3 0
A1 200905 200902 0 0
B1 200905 200908 0 0
B1 200906 200908 1 0
B1 200907 200908 2 0
B1 200910 200908 0 0
A1 200902 200902 3 1
A1 200903 200902 4 2
A1 200904 200902 4 3
A1 200906 200902 1 4
A1 200907 200902 2 5
B1 200908 200908 2 1
B1 200909 200908 2 2
B1 200911 200908 1 3
B1 200912 200908 1 4
Try!