ID repetition with respect to month in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hi,

ID INV Dates
1 0 2017/01/01
1 1 2017/02/01
1 2 2017/03/01
2 5 2016/05/01
3 10 2017/01/01
2 0 2016/04/01
5 2 2017/01/01
2 5 2017/01/01
2 2 2017/10/01

I have above data in my table , and want to calculated number of time id came in month.
My expected result is

ID INV Dates ExpectedResult
1 0 2017/01/01 Null
1 1 2017/02/01 1st
1 2 2017/03/01 2nd
2 5 2016/05/01 1st
3 10 2017/01/01 1st
2 0 2016/04/01 Null
5 2 2017/01/01 1st
2 5 2017/01/01 2nd
2 2 2017/10/01 3rd

What I have tried:

IF(INV > 0) it will start calculation,
as 1st row is having Zero inv it is showing result as null
2nd row, as id-1 came 1st time with inv > 0 must show result as 1st
same id-2 came in next month it is showing result as 2nd and so on

Could you please advise how to achieve this in SQL

Posted 22-Jan-18 1:04am

Member 12673779

Updated 22-Jan-18 17:52pm

RDBurmon

v2

Add a Solution

3 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)

Ambicaprasad Maurya · Answer 1 · 2018-01-22T02:23:00

Hi,

As per my understanding of your question, I have prepared below sql query steps.
Step #1. Get your data into "TmpResult" column in new table2 using ROW_NUMBER(), partition by and order by from table1.
Step #2. Get your expected result using table2 by formatting "TmpResult" column.

Please re-order column names in order by query as per your requirement.
I hope this query may help you get close result which you want.
Thank you.

/*
create table table1(ID int, INV int, Dates datetime)
select * from table1

insert into table1(ID, INV, Dates) values(1, 0 , 2017/01/01)
insert into table1(ID, INV, Dates) values(1, 1 , 2017/02/01)
insert into table1(ID, INV, Dates) values(1, 2 , 2017/03/01)
insert into table1(ID, INV, Dates) values(2, 5 , 2016/05/01)
insert into table1(ID, INV, Dates) values(3, 10, 2017/01/01)
insert into table1(ID, INV, Dates) values(2, 0 , 2016/04/01)
insert into table1(ID, INV, Dates) values(5, 2 , 2017/01/01)
insert into table1(ID, INV, Dates) values(2, 5 , 2017/01/01)
insert into table1(ID, INV, Dates) values(2, 2 , 2017/10/01)

select * from table1 
*/

-- Get data in second table using ROW_NUMBER, partition by and order by
select ID,INV,Dates,(ROW_NUMBER() OVER (partition by ID order by ID,INV,Dates) - 1) TmpResult into table2 from table1 order by ID,INV,Dates

select * from table2

select ID,INV,Dates,TmpResult,
	(case when TmpResult = 0 then NULL 
		else
			(case when TmpResult = 1 then '1st'
				when TmpResult = 2 then '2nd'
				when TmpResult = 3 then '3rd'
			else convert(varchar(5),TmpResult)+'th'
			end) 
	end) ExpectedResult
from table2


/*
drop table table1
drop table table2
*/

CHill60 · Answer 2 · 2018-01-22T05:33:00

An alternative that uses Common Table Expresssions instead of temporary tables and will work with numbers up to a very large number (although I only tested up to 30) i.e. you get 21st instead of 21th

SQL

;with cte as
(
	select id, INV, dates, cast(row_number() OVER (partition by id order by id, dates) as varchar(20)) as r
	from #t1 where INV <> 0
)
select t1.id, t1.inv, t1.dates,  r, 
   r + case when right(r, 1) = 1 and right(r,2) <> '11' and right(r,2) <> '12' then 'st' 
		when right(r,1) = 2 and right(r,2) <> '12' then 'nd' 
		when right(r,1) = 3 and right(r,3) <> '13' then 'rd'
			else 'th' end as ExpectedResult
from #t1 t1
LEFT OUTER JOIN cte ON t1.id = cte.id and t1.dates = cte.dates
order by id, dates

debasish mishra · Answer 3 · 2018-01-22T01:33:00

First you need to select all the items whose Invoice or Inv!=0 and then store them in a temporary table.Then Use for loop and store the Month of date and year,ID and expected Result in 4 temp variables and insert the expected result with other 3 values in an other new temporary table(say New_temptbl) and for each row check if the stored month is greater than the earlier month and years are equal or not then increase the count of expected Result.There should be one else condition where year doesn't match and there you need to replace expected result variable with count 1.Do this until the row search ends.Then Write a union with a select statement where Inv=0;

ID repetition with respect to month in SQL

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0