Brendan Gooden Brendan Gooden - 4 months ago 13
SQL Question

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

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 ║
╚═══════════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╝


Is there a simple way to do this without creating temporary tables?

ZLK ZLK
Answer

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 CustomerInvoice CI
JOIN 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 CustomerInvoice CI
    JOIN 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 master..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.

Comments