# 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.
###### Who is Participating?

Consultant, 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

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  ( s.yearmonth between  (201002 -93) and  201002)
group by s.yearmonth
0

Consultant, 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
``````
0

Author 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

Consultant, 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
``````
0

Consultant, 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
)
group by s.yearmonth
``````
0

Author Commented:
Hi reza_rd

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

Your code is returning empty result set.
0

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

Commented:
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

Consultant, 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
)
group by s.yearmonth
``````
0

Consultant, TrainerCommented:
try my last script , and let me know the result
0

Author Commented:
Tried and the result is

yearmonth      earned_gross_pay
200910      757862
200911      934985
200912      1205340
201001      1319183
201002      1641072
201003      2079008
0

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  ((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

Author Commented:
Hi phh

If we take the yearmonth into variable @yearmonth then

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

Author 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

Commented:
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

Consultant, 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)

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
``````
0

Author 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)
between
and
group by s.yearmonth
``````
0

Consultant, TrainerCommented:
so, problem solved?
Regards,

0

Author Commented:
Hi phh,

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

Commented:
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

Author 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.