--create a new local table and add start date column and separating the month and year in the monthyyear column

Declare @loc_table table (emp int, dates int, year int, amount money,startdate smalldatetime)

Insert into @loc_table

Select emp

,(case when len(monthyyear) = 6 then substring(monthyyear,1,2)

when len(monthyyear) = 5 then substring(monthyyear,1,1)

else ''

end) as dates

,(case when len(monthyyear) = 6 then substring(monthyyear,3,4)

when len(monthyyear) = 5 then substring(monthyyear,2,4)

else ''

end)as year

,amount

,null

from tablename

Update @loc_table

SET startdate = dateadd(month,dates-1,dateadd(yy,year-1900,0))

From @loc_table

--input variables

--need a Query from the month 112013 to 122014

Declare @startdate smalldatetime

,@enddate smalldatetime

,@month1 int

,@year1 int

,@month2 int

,@year2 int

,@montyear1 varchar(10)

,@monthyear2 varchar(10)

SET @montyear1 = 112013

SET @monthyear2 = 122014

--get the start date

SET @month1 = (case when len(@montyear1) = 6 then substring(@montyear1,1,2)

when len(@montyear1) = 5 then substring(@montyear1,1,1)

else ''

end)

SET @year1 = (case when len(@montyear1) = 6 then substring(@montyear1,3,4)

when len(@montyear1) = 5 then substring(@montyear1,2,4)

else ''

end)

SET @startdate = dateadd(month,@month1-1,dateadd(yy,@year1-1900,0))

--get the end date

SET @month2 = (case when len(@monthyear2) = 6 then substring(@monthyear2,1,2)

when len(@monthyear2) = 5 then substring(@monthyear2,1,1)

else ''

end)

SET @year2 = (case when len(@monthyear2) = 6 then substring(@monthyear2,3,4)

when len(@monthyear2) = 5 then substring(@monthyear2,2,4)

else ''

end)

SET @enddate = dateadd(day,-1,dateadd(month,@month2,dateadd(yy,@year2-1900,0)))

select emp, sum(amount) as Amount from @loc_table

where Startdate between @startdate and @enddate

group by emp