I am creating a report chart with Current year Cost and previous year Cost. I also set up two parameters: @DateSelection and @year. The parameter @DateSelection include 3 values: InvoiceDate, ShipDate, and Payment Date. Please help me to correct the following query. Thanks in advance.
SELECT A.TotalCost, A.OrderYear, A.OrderMonth, A.PreTotalCost
from
(SELECT LEFT(@DateSelection, 4) as OrderYear, RIGHT(LEFT(@DateSelection, 6), 2) as OrderMonth , AVG(Cost) as TotalCost, LAG(AVG(Cost), 12) OVER(ORDER BY LEFT(@DateSelection, 4), RIGHT(LEFT(@DateSelection, 6), 2)) as PreTotalCost
FROM Table1
GROUP BY LEFT(@DateSelection, 4), RIGHT(LEFT(@DateSelection, 6), 2)
) A
WHERE A.OrderYear = @year