Sql-server – Show Monthly Revenue By EmployeeID

sql serversql-server-2008-r2t-sql

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:

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

Related Question