I have a database with sale data for the past 5 years, 2012 – current. I am in need of showing a breakdown by employeeID and the monthly sale amount. I know this syntax will work, but it is going to be quite cumbersome to add a new case statement every time we roll into a new month. Is there a quicker/more efficient way to accomplish this?
This is sample DDL (sample as it is only a very few rows)…
Declare @Monetary Table(empID varchar(10), empsaleamt float, empsaledate date)
Insert Into @Monetary (empID, empsaleamt, empsaledate) Values
('123abc', 100.00, '20160101'), ('123abc', 200.00, '20160101'), ('123abc', 300.00, '20160201')
,('456cde', 200.00, '20160101'), ('456cde', 100.00, '20160201'), ('456cde', 100.00, '20160301')
Select
empID
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '01') Then empsaleamt Else 0 End) As Jan16Sales
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '02') Then empsaleamt Else 0 End) As Feb16Sales
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '03') Then empsaleamt Else 0 End) As Mar16Sales
FROM @Monetary
Group By empID
ORDER By empID ASC
Best Answer
You could consider to use a PIVOT solution:
dbfiddle here