Show sum of sales for each customer for each month and year dynamically

Question

Not sure how to get the sum of each customers' orders broken up by dynamic month-year columns for the last 12/13 months from todays date

Manually hard coding the months and defining the year works fine but not able to show it dynamically

`Declare @SQLQuery as NVARCHAR(MAX)
Declare @PivotColumns as NVARCHAR(Max)

select @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(DATENAME(MONTH, a.INVOICEDATE))
from dbo.TableA a,dbo.TableB b, dbo.TableC c
where a.INVOICEID=b.INVOICENUMBER and b.INVOICECUSTAC=c.CACCOUNT


set @SQLQUERY= N'select b.INVOICECUSTOMERACCOUNTNUMBER, c.ORGANIZATIONNAME, SUM(a.LINEAMOUNTTOTAL) [LineAmount]
from dbo.TableA a,dbo.TableB b, dbo.TableC c
where a.INVOICEID=b.INVOICENUMBER and b.INVOICECUSTAC=c.CACCOUNT 
group by b.INVOICECUSTAC,c.ORGANIZATIONNAME,a.INVOICEDATE) as x
PIVOT (SUM(LineAmount) FOR Month IN (' + @PivotColumns +')) AS P'

exec sp_executesql @SQLQUERY`

The query keeps running without any result being displayed.

The expected outcome should be something like:

CACCOUNT JULY-18 AUGUST-18.... AUGUST-19
ABC001 50000
DEF002 70000

PRINT your `@SQLQUERY` right before the `EXEC` and see what it is you're trying to execute and whether it makes sense. — Tab Alleman, Aug 27 '19 at 15:18
Hi @TabAlleman, it is just saying executing query without giving any output for me to check — Keval Shah, Aug 27 '19 at 15:22
Sounds like you didn't follow my suggestion to PRINT the query. — Tab Alleman, Aug 27 '19 at 15:26
If you did that and didn't see any result, then probably you've concatenated with NULL somewhere, making your entire query string NULL. — Tab Alleman, Aug 27 '19 at 15:31
you can refer : [nice dynamic pivot answer for SQL Server](https://stackoverflow.com/a/10404455/5841306) — Barbaros Özhan, Aug 27 '19 at 15:39

score 0 · Answer 1 · answered Aug 27 '19 at 15:42

0

I think you need to replace parameter @PivotColumns setting part with following query.

select @PivotColumns = 
        STRING_AGG (
                    (QUOTENAME ( (isnull( (DATENAME(MONTH, a.INVOICEDATE )), '')  ) ) ), 
                    ','
                  )
from    dbo.TableA a
            join dbo.TableB b on a.INVOICEID=b.INVOICENUMBER
            join dbo.TableC c on b.INVOICECUSTAC=c.CACCOUNT

Print @PivotColumns;

answered Aug 27 '19 at 15:42

Shekar Kola

1,287
9
15

But would this give me month columns for the last 13 months? – Keval Shah Aug 27 '19 at 15:45
@KevalShah, it depends on the data available in yours tables (`TableA`, `TableB`, `TableC`), you may want to add `where` and `order by` clause to the same query to get particular period of months – Shekar Kola Aug 27 '19 at 15:49

Show sum of sales for each customer for each month and year dynamically

1 Answers1