SQL last 15 months of sales grouped by customer

Question

I would like to output in sql a table that contains the monthly sales per customer. Each column should correspond to another month.

First column should be current month example my sql columns are:

customer
invoicedate
sales

Original table looks like this:

+------------+------------+---------+
|  Customer  |  invdate   | sales   |
+------------+------------+---------+
| Best Buy   | 03-12-2019 |   433   |
| Walmart    | 03-15-2019 |   543   |
| Home Depot | 12-12-2018 |      32 |
+------------+------------+---------+

Desired Output:

+------------+----------+--------+--------+--------+--------+----------+
|  Customer  | March 19 | Feb 19 | Jan 19 | Dec 18 | Nov 18 | Oct 18   |
+------------+----------+--------+--------+--------+--------+----------+
| Home Depot |      100 |    300 |    244 |     32 |    322 |     43   |
| Walmart    |      543 |    222 |    234 |     12 |    234 |     34   |
| Bestbuy    |      433 |    323 |    323 |     23 |    433 |     34   |
+------------+----------+--------+--------+--------+--------+----------+

You can do this using PIVOT in SQL. follow below links for examples and help.[link]https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — niks, Mar 05 '19 at 03:27
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Sam M, Mar 05 '19 at 03:28
Hi you have to use pivot with dynamic sql with execute see Microsoft documentation and after share your query — Sanpas, Mar 05 '19 at 06:35
In what tool is this output being rendered? While a dynamic SQL Statement would solve the problem, it also starts to introduce formatting of the data in SQL; which is generally better suited to do in the display/reporting tool. — xQbert, Mar 08 '19 at 16:33

Hogan · Answer 1 · 2019-03-05T03:44:09.110

You can use group by, sum, and case to good effect, like this:

select
  customer,
  sum(case when year(invdate) = year(getdate) and month(invdate) = month(getdate())   then sales else o end),
  sum(case when year(invdate) = year(dateadd(m,1,getdate))
           and month(invdate) = month(dateadd(m,1,getdate())) then sales else o end),
  sum(case when year(invdate) = year(dateadd(m,2,getdate))
           and month(invdate) = month(dateadd(m,2,getdate())) then sales else o end),
  sum(case when year(invdate) = year(dateadd(m,3,getdate))
           and month(invdate) = month(dateadd(m,3,getdate())) then sales else o end),
  sum(case when year(invdate) = year(dateadd(m,4,getdate))
           and month(invdate) = month(dateadd(m,4,getdate())) then sales else o end),
  sum(case when year(invdate) = year(dateadd(m,5,getdate))
           and month(invdate) = month(dateadd(m,5,getdate())) then sales else o end)
from tableyoudidnotgivethenameof
group by customer

this actually worked very well. I just had to change the dateadd to substract months not add them. the only other question i have is how do I have dynamic column names, to display the current month, last month, etc — Isaac, Mar 05 '19 at 16:44
The only way to have dynamic column names is to use dynamic SQL. put everything above in a string and add an `AS YYYY` for where you want the dynamic names -- then do an exec on the resulting string. — Hogan, Mar 05 '19 at 21:04

SQL last 15 months of sales grouped by customer

1 Answers1