Filling a table with monthly sales amounts for the last 12 months from SQL

Question

I currently have a query which lists the sum sales for the past 12 months

SELECT
  TOP 10 SUM(CustomerInvoice.Total) AS 'Total', CustomerName
FROM dbo.CustomerInvoice
INNER JOIN dbo.Customer
  ON CustomerInvoice.BillToCode = Customer.CustomerCode
WHERE InvoiceDate BETWEEN CAST(dateadd(year, -1, getdate())  AS SMALLDATETIME) AND CAST(GETDATE() AS SMALLDATETIME) 
  GROUP BY CustomerName
  ORDER BY Total DESC

This returns a table like so.

╔═══════════╦═══════════════╗
║ Total     ║ Customer Name ║
╠═══════════╬═══════════════╣
║ 806405.85 ║ Customer 1    ║
╠═══════════╬═══════════════╣
║ 128244.77 ║ Customer 2    ║
╠═══════════╬═══════════════╣
║ 80127.75  ║ Customer 3    ║
╠═══════════╬═══════════════╣
║ 71994.56  ║ Customer 4    ║
╠═══════════╬═══════════════╣
║ 67641.31  ║ Customer 5    ║
╠═══════════╬═══════════════╣
║ 67064.04  ║ Customer 6    ║
╠═══════════╬═══════════════╣
║ 60324.39  ║ Customer 7    ║
╠═══════════╬═══════════════╣
║ 59684.7   ║ Customer 8    ║
╠═══════════╬═══════════════╣
║ 59066.31  ║ Customer 9    ║
╠═══════════╬═══════════════╣
║ 57112.04  ║ Customer 10   ║
╚═══════════╩═══════════════╝

What I'm needing is the monthly sales for each of these customers, in a table format like this:

╔═══════════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╗
║ ///////////// ║ Jul-15  ║ Aug-15  ║ Sep-15  ║ Oct-15  ║ Nov-15  ║ Dec-15  ║ Jan-16  ║ Feb-16  ║ Mar-16  ║ Apr-16  ║ May-16  ║ Jun-16  ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 1    ║ 2087.23 ║ 4289.49 ║ 2326.14 ║ 1058.48 ║ 552.03  ║ 2438.16 ║ 7146.80 ║ 9305.66 ║ 8800.45 ║ 7199.57 ║ 1247.92 ║ 8977.39 ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 2    ║ 36.28   ║ 9327.53 ║ 2385.92 ║ 9372.04 ║ 2877.75 ║ 543.23  ║ 5464.15 ║ 4426.06 ║ 93.84   ║ 843.24  ║ 6895.55 ║ 74.02   ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 3    ║ 9492.12 ║ 9285.28 ║ 4528.11 ║ 7198.50 ║ 4037.02 ║ 5160.75 ║ 8246.33 ║ 6806.81 ║ 7051.14 ║ 814.43  ║ 9631.44 ║ 804.93  ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 4    ║ 9217.58 ║ 9147.28 ║ 2202.67 ║ 6432.05 ║ 2365.12 ║ 4973.94 ║ 5486.00 ║ 1793.43 ║ 1284.91 ║ 3671.67 ║ 751.11  ║ 5483.26 ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 5    ║ 3211.68 ║ 3329.07 ║ 2319.50 ║ 5077.95 ║ 8977.03 ║ 6813.39 ║ 4890.97 ║ 345.05  ║ 483.89  ║ 565.10  ║ 7760.01 ║ 6465.55 ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 6    ║ 1683.52 ║ 4287.37 ║ 524.30  ║ 4595.54 ║ 1102.11 ║ 4623.11 ║ 3165.83 ║ 2363.13 ║ 2584.55 ║ 3420.01 ║ 9496.31 ║ 2940.19 ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 7    ║ 1325.93 ║ 1847.88 ║ 3107.79 ║ 1855.39 ║ 7698.80 ║ 5360.15 ║ 7752.69 ║ 8289.06 ║ 8444.03 ║ 2719.06 ║ 6187.75 ║ 4921.10 ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 8    ║ 7573.35 ║ 2198.58 ║ 3432.29 ║ 6427.38 ║ 3856.54 ║ 9164.15 ║ 4945.19 ║ 9812.18 ║ 4712.47 ║ 5530.55 ║ 2396.45 ║ 2914.75 ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 9    ║ 8278.74 ║ 4865.93 ║ 4071.40 ║ 5628.76 ║ 3202.43 ║ 7270.84 ║ 811.55  ║ 275.52  ║ 3451.58 ║ 8164.47 ║ 8117.53 ║ 66.86   ║
╠═══════════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ Customer 10   ║ 581.96  ║ 8288.06 ║ 521.93  ║ 8938.67 ║ 3211.10 ║ 348.94  ║ 6841.85 ║ 3282.51 ║ 2007.62 ║ 1621.19 ║ 5982.00 ║ 3841.82 ║
╚═══════════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╝

I have this query, but is there any way I can have the query dynamically choose columns for the past 12 months?

SELECT TOP 100 * FROM(
SELECT CustomerName
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2015 AND DATEPART(MONTH, InvoiceDate) = 8    THEN Total ELSE 0 END) AS [Aug-15]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2015 AND DATEPART(MONTH, InvoiceDate) = 9    THEN Total ELSE 0 END) AS [Sep-15]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2015 AND DATEPART(MONTH, InvoiceDate) = 10   THEN Total ELSE 0 END) AS [Oct-15]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2015 AND DATEPART(MONTH, InvoiceDate) = 11   THEN Total ELSE 0 END) AS [Nov-15]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2015 AND DATEPART(MONTH, InvoiceDate) = 12   THEN Total ELSE 0 END) AS [Dec-15]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2016 AND DATEPART(MONTH, InvoiceDate) = 1    THEN Total ELSE 0 END) AS [Jan-16]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2016 AND DATEPART(MONTH, InvoiceDate) = 2    THEN Total ELSE 0 END) AS [Feb-16]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2016 AND DATEPART(MONTH, InvoiceDate) = 3    THEN Total ELSE 0 END) AS [Mar-16]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2016 AND DATEPART(MONTH, InvoiceDate) = 4    THEN Total ELSE 0 END) AS [Apr-16]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2016 AND DATEPART(MONTH, InvoiceDate) = 5    THEN Total ELSE 0 END) AS [May-16]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2016 AND DATEPART(MONTH, InvoiceDate) = 6    THEN Total ELSE 0 END) AS [Jun-16]
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = 2016 AND DATEPART(MONTH, InvoiceDate) = 7    THEN Total ELSE 0 END) AS [Jul-16]
, SUM(CASE WHEN InvoiceDate BETWEEN CAST(dateadd(year, -1, getdate())  AS SMALLDATETIME) AND CAST(GETDATE() AS SMALLDATETIME) THEN CI.Total ELSE 0 END) AS 'Total'
 FROM CustomerInvoice CI
  JOIN Customer C ON CI.BillToCode = C.CustomerCode
  GROUP BY C.CustomerName) A
  ORDER BY Total DESC

pivoting or using conditional aggregation will get what you want... but if it's something that needs to dynamically produce the column names (e.g. the last 12 months at any given time), it will need to use dynamic SQL — ZLK, Jul 18 '16 at 00:01
Thanks @ZLK. Any more info you can give me on dynamic SQL, or point me to some tutorials or documentation on how to use it? I'm using SQL Server 2008 — Brendan Gooden, Jul 18 '16 at 00:05
Refer to this http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — sqluser, Jul 18 '16 at 00:19
I'm not really sure about specific documentation on how to use it, but there's a variety of places you'll find information on it (just by googling it or searching SO itself). Essentially, what you want to do is declare a variable (e.g. `@cols`), make `@cols` your conditional aggregation select, declare a variable for the execution of the dynamic SQL (e.g. `@SQL`), write the select statement into `@SQL` and execute it. I'll write an example out. — ZLK, Jul 18 '16 at 01:08
Thanks heaps for that. If you can show me an example in an answer below I'll accept it and give you a vote for your efforts. — Brendan Gooden, Jul 18 '16 at 01:10

ZLK · Accepted Answer · 2016-07-18T03:10:11.873

To produce the column names, create a variable like so:

DECLARE @cols VARCHAR(4000) = '';
SELECT @cols += '
, SUM(CASE WHEN DATEPART(YEAR, InvoiceDate) = ' + CAST(DATEPART(YEAR, DATEADD(MONTH, -number, GETDATE())) AS CHAR(4)) + ' AND DATEPART(MONTH, InvoiceDate) = ' + CAST(DATEPART(MONTH, DATEADD(MONTH, -number, GETDATE())) AS CHAR(4)) + ' THEN Total ELSE 0 END) AS ' + QUOTENAME(LEFT(DATENAME(MONTH, DATEADD(MONTH, -number, GETDATE())), 3) + '-' + RIGHT(DATEPART(YEAR, DATEADD(MONTH, -number, GETDATE())), 2))
FROM master..spt_values
WHERE number < 12
AND type = 'p'
ORDER BY number DESC;
--PRINT @cols;

What this is doing is producing a part of the select statement to be used in the dynamic SQL. This is your conditional aggregation.

Then you write out your dynamic SQL and execute it, like so...

DECLARE @SQL NVARCHAR(MAX) = 'SELECT CustomerName' + @cols + '
FROM dbo.CustomerInvoice CI
JOIN dbo.Customer C ON CI.BillToCode = C.CustomerCode
WHERE InvoiceDate BETWEEN CAST(DATEADD(YEAR, -1, GETDATE()) AS SMALLDATETIME) AND CAST(GETDATE() AS SMALLDATETIME)
AND CustomerName IN (
    SELECT TOP 10 CustomerName
    FROM dbo.CustomerInvoice CI
    JOIN dbo.Customer C ON CI.BillToCode = C.CustomerCode
    WHERE InvoiceDate BETWEEN CAST(DATEADD(YEAR, -1, GETDATE()) AS SMALLDATETIME) AND CAST(GETDATE() AS SMALLDATETIME)
    ORDER BY SUM(CI.Total) DESC)
GROUP BY CustomerName
ORDER BY SUM(CI.Total) DESC';
--PRINT @SQL;

EXEC sp_executesql @SQL;

Essentially, you need to know what the top 10 customers are for a given year (the IN statement will tell you this), but you need the outer query to tell the date breakdown.

I'm getting a `invalid object name CustomerInvoice` error. This is the output from my @SQL - https://codeshare.io/OLt1r — Brendan Gooden, Jul 18 '16 at 01:47
The error occurs after the FROM CustomerInvoice CI on the second line of your second code block — Brendan Gooden, Jul 18 '16 at 01:50
@BrendanGooden I readded the schema (dbo), since that seems like it might be causing the error. — ZLK, Jul 18 '16 at 02:15
still showing the same error. I have edited my post, I was able to get the result I needed, but just now need a way to update columns dynamically for the last 12 months. — Brendan Gooden, Jul 18 '16 at 02:45
Oops. It shouldn't be `EXEC master..sp_executesql` it should just be `EXEC sp_executesql`. I did a quick test on a sample table I created to check it would work and forgot about that line. Edited now. — ZLK, Jul 18 '16 at 03:11

Filling a table with monthly sales amounts for the last 12 months from SQL

1 Answers1