15,958,494 members
1.00/5 (1 vote)
See more:
In my table Im having User, Month, LMonth columns. I have to generate RowNumber order by month where the month should be greater than or equal to LMonth.

Ex.
User Month Lmonth RN
A1 200810 200902 0
A1 200811 200902 0
A1 200812 200902 0
A1 200901 200902 0
A1 200902 200902 1
A1 200903 200902 2
A1 200904 200902 3
A1 200905 200902 4
A1 200906 200902 5
A1 200907 200902 6
B1 200905 200908 0
B1 200906 200908 0
B1 200907 200908 0
B1 200908 200908 1
B1 200909 200908 2
B1 200910 200908 3
B1 200911 200908 4
B1 200912 200908 5

My required column is RN.
For User A1 the RN should start from 200902 [LMonth] of Month
If ARR=0 RN has to start again from 1

What I have tried:

select User,Month,LMonth,ROW_NUMBER() over(order by month)RN from MyTable

I need the condition RN should start where Month=Lmnoth
Posted
Updated 15-Jun-16 18:51pm
v4

## Solution 1

You could perform the row numbering on just the rows that need it then union with the rest. E.g.
SQL
```select [User], [Month], Lmonth,
ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY Lmonth) RN
FROM MyTable WHERE [Month] >= lMonth
UNION ALL
select [User], [Month], Lmonth, 0
FROM MyTable WHERE [Month] < lMonth
ORDER BY 1,4```

Comments
kirthiga S 15-Jun-16 6:14am
Thank you.. It works.
I need one additional query. If ARR=0 then RN again starts from 1
CHill60 15-Jun-16 7:48am
I'm struggling with this one! Obviously it could be done using a CURSOR but there must be a way to do it without a cursor. I suggest asking a new question ... show the code from the solution and your new data (with column ARR) and your expected results. It's also worth mentioning this post just to point out that the requirements have changed and you are not reposting.
Note: I think there is an error in your expected results
B1 200912 200908 1 5
should be
B1 200912 200908 1 2 ?
kirthiga S 15-Jun-16 7:57am
Thanks for your suggestion.. I post this as new query

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 48 SG Tamilan 36 Andre Oosthuizen 30 merano99 10 Malcolm Swaine 10
 OriginalGriff 108 Pete O'Hanlon 70 Andre Oosthuizen 30 merano99 30 SG Tamilan 26

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900