Avatar of ITNC
ITNC
Flag for United States of America

asked on 

Dynamic SQL Query with Pivot Chart

I'm having an issue.   This is what i'm seeing:

Must declare the scalar variable "@PivotWhen0"  i'm seeing this for all of the @PivotWhen0/1/2/3/4/5, etc.... can someone please help

SELECT DISTINCT TOP 100000
DATEPART(year, [dbo].[v_rpt_Invoices].[Date_Invoice]) AS 'Year', SUM([dbo].[v_rpt_Invoices].[Time_Amount]) AS 'Billed Time', SUM([dbo].[v_rpt_Invoices].[Misc_Amount]) AS 'Billed Product', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen0 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Jan', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen1 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Feb', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen2 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Mar', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen3 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Apr', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen4 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'May', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen5 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Jun', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen6 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Jul', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen7 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Aug', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen8 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Sep', SUM(CASE DATEPART(month, [dbo].[v_rpt_Invoices].[Date_Invoice]) WHEN @PivotWhen9 THEN [dbo].[v_rpt_Invoices].[Invoice_Amount] ELSE 0 END) as 'Oct', SUM([dbo].[v_rpt_Invoices].[Invoice_Amount]) as 'Total'
FROM [dbo].[v_rpt_Invoices] WITH(NOLOCK)
WHERE ([dbo].[v_rpt_Invoices].[Status_Description] IN ('Closed','Closed - Emailed','New','Write Off')) AND ([dbo].[v_rpt_Invoices].[Date_Invoice] BETWEEN '2016-01-01T00:00:00.000' AND '2016-12-31T23:59:59.998')
GROUP BY DATEPART(year, [dbo].[v_rpt_Invoices].[Date_Invoice])
ORDER BY DATEPART(year, [dbo].[v_rpt_Invoices].[Date_Invoice]) ASC;
Microsoft SQL ServerSSRS

Avatar of undefined
Last Comment
ITNC

8/22/2022 - Mon