2

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
4

You could consider to use a PIVOT solution:

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'),
('123abc', 100.00, '20170101'), 
('123abc', 200.00, '20170101'), 
('123abc', 300.00, '20170201'),
('456cde', 200.00, '20170101'), 
('456cde', 100.00, '20170201'), 
('456cde', 100.00, '20170301');

SELECT ID, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM 
    (SELECT CONCAT(empId,'-',CAST(YEAR(empsaledate) AS varchar(10))) as ID, 
            month(empsaledate) AS months, empsaleamt
     FROM   @monetary ) src
PIVOT 
    (SUM(empsaleamt) FOR months IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pvt;

GO
ID          |   1 |   2 |    3 |    4 |    5 |    6 |    7 |    8 |    9 |   10 |   11 |   12
:---------- | --: | --: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---:
123abc-2016 | 300 | 300 | null | null | null | null | null | null | null | null | null | null
123abc-2017 | 300 | 300 | null | null | null | null | null | null | null | null | null | null
456cde-2016 | 200 | 100 |  100 | null | null | null | null | null | null | null | null | null
456cde-2017 | 200 | 100 |  100 | null | null | null | null | null | null | null | null | null

dbfiddle here

5
  • Would I need an individual pivot for each year? Jun 28 '17 at 16:09
  • No, but then maybe you should use a dynamic query. PIVOT requires a well known column's name
    – McNets
    Jun 28 '17 at 16:12
  • the column names remain the same, just the dates range from 2015 to 2017 and will continue into 2018 etc Jun 28 '17 at 16:13
  • 1
    Or compose an ID as [EmpID] + [Year].
    – McNets
    Jun 28 '17 at 16:13
  • Excellent solution! I will go that route, thank you! Jun 28 '17 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.