Bhuvnesh (10/13/2013)
declare @tbl table(
emp int ,
monthyear int,
amount money
)
insert into @tbl
select 1, 102013 ,1000
union all select 2, 102013, 1000
union all select 1, 112013 ,1000
union all select 1, 112013, 1000
union all select 2, 122013 ,1000
union all select 2 ,122013,0000
select emp, SUM(amount) from @tbl
where LEFT(monthyear,2) = '11' or LEFT(monthyear,2) = '12'
group by emp
I'm thinking that there's a major problem with that solution. What is the absolute guarantee that data from another year won't be included in the table? The code also contains a non-SARGable WHERE clause that will guarantee a table scan rather than a seek and range scan.
The absolute best thing to do here would be to store the date data correctly. Using INT to store date data isn't the best way. It could certainly be improved by storing the date in the INT in a sortable fashion (yyyymm instead of mmyyyy) but that's still not the best way.
--Jeff Moden
Change is inevitable... Change for the better is not.