How to arrive rownumber with multiple conditions

Question

3.00/5 (2 votes)

See more:

SQL-Server-2012

I have to arrive rownumber for following conditions

C#

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 200902 200902 3 1
A1 200903 200902 4 2
A1 200904 200902 4 3
A1 200905 200902 0 0
A1 200906 200902 1 1
A1 200907 200902 2 2
B1 200905 200908 0 0
B1 200906 200908 1 0
B1 200907 200908 2 0
B1 200908 200908 2 1
B1 200909 200908 2 2
B1 200910 200908 0 0
B1 200911 200908 1 1
B1 200912 200908 1 2

In my above table I have to arrive RN column. The conditions are
1. RN should starts when Month >= LMonth
2. RN should be 0 If ARR = 0 and RN restarts from 1 when ARR > 0

What I have tried:

select User,Month,Lmonth,ARR,ROW_NUMBER() OVER (PARTITION BY User ORDER BY Month)RN FROM MyTable WHERE Month >= LMonth
UNION ALL
select User,Month,Lmonth,ARR,0 FROM MyTable WHERE Month < LMonth
order by 1,2

Iam struggling with second condition.

Posted 15-Jun-16 2:11am

kirthiga S

Updated 15-Jun-16 11:18am

v2

Add a Solution

Comments

Maciej Los 15-Jun-16 16:00pm

What is expected output?
Is there any primary key?

kirthiga S 16-Jun-16 0:57am

RN column is my expected output. For each User Month should be unique.
Ex. For User A1 Month starts from 200810 to 200907.

2 solutions

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)

Richard MacCutchan · Answer 1 · 2016-06-15T04:30:00

Solution 1

You have already been given an answer in your original of this question at How to generate rownumber with specific conditions[^].

If you need further help then reply to the solution in that question, rather than reposting.

Posted 15-Jun-16 4:30am

Richard MacCutchan

Comments

CHill60 15-Jun-16 11:07am

I advised him to post a separate question - if you note on the other post there is no ARR column - OP wishes the Row Numbering to restart when ARR=0 is encountered.
The solution posted at the other post does not take into account column ARR (which was not mentioned in the original post - see the comments)

tl;dr; This is a subtly different problem to the one on the link

kirthiga S 16-Jun-16 0:59am

Both questions are different

Maciej Los · Answer 2 · 2016-06-15T11:18:00

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:

SQL

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!