Show salary of current month and previous six months

The result of

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ( s.yearmonth between  201002 -(6) and  201002)
group by s.yearmonth

and the result is
yearmonth      earned_gross_pay
201001      1319183
201002      1641072

I intended the result to be

yearmonth      earned_gross_pay
200909      735000
200910      357000
200911      753000
200912      123000
201001      1319183
201002      1641072

i.e I wanted to see the salary of current month 201002 and salary of five previous months.

The data type of column yearmonth is numeric
where first four characters represent year and last 2 month.

I am unable to construct where clause,
I tried  
where  ( s.yearmonth between  201002 -(6) and  201002)
but this is not working.
MehramAsked:
Who is Participating?
 
Reza RadConsultant, TrainerCommented:
so this is correct.
isn't?

currect date is: 201004
and the results is between 200910  and 201004

if you want to change the distance between month, find -6 in my script and change it to another digit, this means -6 months from current date
0
 
AbarajCommented:
select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ( s.yearmonth between  (201002 -93) and  201002)
group by s.yearmonth
0
 
Reza RadConsultant, TrainerCommented:
try this:

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ( s.yearmonth between  dateadd(Month,-6,convert(datetime,substring('201002',1,4)+'-'+SUBSTRING('201002',5,2)+'-01')) -(6) and  convert(datetime,substring('201002 ',1,4)+'-'+SUBSTRING('201002 ',5,2)+'-01'))
group by s.yearmonth

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MehramAuthor Commented:
Hi abaraj

<<where  ( s.yearmonth between  (201002 -93) and  201002)>>

This will not work for all current months

<<where  ( s.yearmonth between  (200912 -93) and  200912)>>
is returning 12 rows.

0
 
Reza RadConsultant, TrainerCommented:
sorry, try this one:

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ( s.yearmonth between  dateadd(Month,-6,convert(datetime,substring('201002',1,4)+'-'+SUBSTRING('201002',5,2)+'-01')) and  convert(datetime,substring('201002 ',1,4)+'-'+SUBSTRING('201002 ',5,2)+'-01'))
group by s.yearmonth

Open in new window

0
 
Reza RadConsultant, TrainerCommented:
final version:

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ( 
	convert(datetime,substring(s.yearmonth,1,4)+'-'+SUBSTRING(s.yearmonth,5,2)+'-01') between  
	dateadd(Month,-6,convert(datetime,substring('201002',1,4)+'-'+SUBSTRING('201002',5,2)+'-01')) and  	convert(datetime,substring('201002 ',1,4)+'-'+SUBSTRING('201002 ',5,2)+'-01')
	)
group by s.yearmonth

Open in new window

0
 
MehramAuthor Commented:
Hi reza_rd

as I said in my question the column yearmonth is numeric.

Your code is returning empty result set.
0
 
MehramAuthor Commented:
select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  (
        convert(datetime,substring(s.yearmonth,1,4)+'-'+SUBSTRING(s.yearmonth,5,2)+'-01') between  
        dateadd(Month,-6,convert(datetime,substring('201002',1,4)+'-'+SUBSTRING('201002',5,2)+'-01')) and       convert(datetime,substring('201002 ',1,4)+'-'+SUBSTRING('201002 ',5,2)+'-01')
        )
group by s.yearmonth

Msg 8116, Level 16, State 1, Line 1
Argument data type numeric is invalid for argument 1 of substring function.

0
 
AbarajCommented:
or try to use soming like this...
select convert(varchar,year(getdate()))+case when (len(convert(varchar,month(getdate())))=1) then '0'+convert(varchar,month(getdate())) else convert(varchar,month(getdate()))end

 
0
 
Reza RadConsultant, TrainerCommented:
AND THIS WILL RETURN ALL RECORDS BETWEEN CURRECT DATE AND 6 MONTH BEFORE:

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ( 
	s.yearmonth between  
	SUBSTRING(convert(char(10),dateadd(Month,-6,GETDATE()),112),1,6) and  SUBSTRING(convert(char(10),GETDATE(),112),1,6)
	)
group by s.yearmonth

Open in new window

0
 
Reza RadConsultant, TrainerCommented:
try my last script , and let me know the result
0
 
MehramAuthor Commented:
Tried and the result is

yearmonth      earned_gross_pay
200910      757862
200911      934985
200912      1205340
201001      1319183
201002      1641072
201003      2079008
0
 
phhCommented:
Please try:

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ((201002 %100>=6) and ( s.yearmonth between  201002 -(6) and  201002)) or ((201002 %100<6) and ( s.yearmonth between  201002 -(93) and  201002))
group by s.yearmonth
0
 
MehramAuthor Commented:
Hi phh

If we take the yearmonth into variable @yearmonth then

your code is working
declare @yearmonth bigint
set @yearmonth = 200912


select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ((@yearmonth %100>=6) and ( s.yearmonth between  @yearmonth -(6) and  @yearmonth)) or ((@yearmonth %@yearmonth<6)
and ( s.yearmonth between  @yearmonth -(93) and  @yearmonth))
group by s.yearmonth


wghere @yearmonth = 201004,201003,20102,201001
it is fialing for @yearmonth = 200912 (12rows)
it is fialing for @yearmonth = 200911 (11rows)
0
 
MehramAuthor Commented:
hi reza_red

<<so this is correct.
isn't?

currect date is: 201004
and the results is between 200910  and 201004>>

Yes it is currect.

Pl. take 201004 into variable @yearmonth(bigint) and pl give me the modifed script as per your logic.

I tried to modify your code but could not succeed.

0
 
phhCommented:
I'm afraid there was a little mistake in your code. Could you please try to change from:

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ((@yearmonth %100>=6) and ( s.yearmonth between  @yearmonth -(6) and  @yearmonth)) or ((@yearmonth %@yearmonth<6)
and ( s.yearmonth between  @yearmonth -(93) and  @yearmonth))
group by s.yearmonth

to:

select
 yearmonth = s.yearmonth
, earned_gross_pay =  sum(s.earned_gross_pay)
from salary s
join employee   e  on (  s.emp_code = e.emp_code )
join department d  on ( e.dept_code =  d.dept_code)
where  ((@yearmonth %100>=6) and ( s.yearmonth  between  @yearmonth -(6) and  @yearmonth)) or ((@yearmonth  %100<6)
and ( s.yearmonth between  @yearmonth -(93) and   @yearmonth))
group by s.yearmonth
0
 
Reza RadConsultant, TrainerCommented:
actually I didn't understand what you mean exactly,

but I made some variables , and put values there,
you can change the @monthsBetween to change the number of months between two dates,

Is it what you want?

declare @curDate char(6)
,@preDate char(6)
declare @monthsBetween int

set @monthsBetween=-6

set @curDate=SUBSTRING(convert(char(10),GETDATE(),112),1,6) 
set @preDate=SUBSTRING(convert(char(10),dateadd(Month,@monthsBetween,GETDATE()),112),1,6)

select @curDate,@preDate

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where  ( 
	s.yearmonth between  
	@preDate and  @curDate
	)
group by s.yearmonth

Open in new window

0
 
MehramAuthor Commented:
Hi Reza_red

Taking idea from your code I succeeded to construct the where clause.

I needed to construct the where clause when yearmonth is provided in number and not in datetime
as you did in your code with getdate()
declare @yearmonth bigint
set @yearmonth = 200908

select
 yearmonth = s.yearmonth
, earned_gross_pay = sum(s.earned_gross_pay)
from salary s
join employee   e  on ( s.emp_code = e.emp_code )
join department d  on ( e.dept_code = d.dept_code)
where dateadd(d,0,convert(varchar,s.yearmonth)+'01') 
between
dateadd(m,-5,convert(varchar,@yearmonth)+'01') 
and
dateadd(d,0,convert(varchar,@yearmonth)+'01') 
group by s.yearmonth

Open in new window

0
 
Reza RadConsultant, TrainerCommented:
so, problem solved?
Regards,

0
 
MehramAuthor Commented:
Hi phh,

I tried your code

declare @yearmonth bigint
set @yearmonth = 201003


select
yearmonth = s.yearmonth
, earned_gross_pay =  sum(s.earned_gross_pay)
from salary s
join employee   e  on (  s.emp_code = e.emp_code )
join department d  on ( e.dept_code =  d.dept_code)
where  ((@yearmonth %100>=6) and ( s.yearmonth  between  @yearmonth -(6) and  @yearmonth)) or ((@yearmonth  %100<6)
and ( s.yearmonth between  @yearmonth -(93) and   @yearmonth))
group by s.yearmonth

It is returning 6 rows for 200906,200905,200904,200903,200902,200901 and it is correct.
It is returning 7 rows for 200912,200911,200910,200909,200908,200907 ( I row additional )


0
 
phhCommented:
Yeah, sorry that I did not check it carefully. It should be -(5) instead of -(6), i.e.:

select
yearmonth = s.yearmonth
, earned_gross_pay =  sum(s.earned_gross_pay)
from salary s
join employee   e  on (  s.emp_code = e.emp_code )
join department d  on ( e.dept_code =  d.dept_code)
where  ((@yearmonth %100>=6) and ( s.yearmonth  between  @yearmonth -(5) and  @yearmonth)) or ((@yearmonth  %100<6)
and ( s.yearmonth between  @yearmonth -(93) and   @yearmonth))
group by s.yearmonth
0
 
MehramAuthor Commented:
Thanks and very best regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.