0

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

Keval Shah
  • 41
  • 2

1 Answers1

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;
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