How to generate rownumber with specific conditions

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

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 14-Jun-16 23:32pm

kirthiga S

Updated 15-Jun-16 18:51pm

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2016-06-14T23:43:00

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

Posted 14-Jun-16 23:43pm

CHill60

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