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