SQL Query for a PIVOT report

[halaiadnan]

Hi

I have written a query for the query manager below.

DECLARE @ItemCode NVARCHAR(255)

SET @ItemCode = 'MyItemCode'

SELECT

MONTH([DocDate]) AS [Month],

SUM(CASE WHEN YEAR([DocDate]) = 2015 THEN [Quantity] END) AS [2015],

SUM(CASE WHEN YEAR([DocDate]) = 2016 THEN [Quantity] END) AS [2016],

SUM(CASE WHEN YEAR([DocDate]) = 2017 THEN [Quantity] END) AS [2017],

SUM(CASE WHEN YEAR([DocDate]) = 2018 THEN [Quantity] END) AS [2018],

SUM(CASE WHEN YEAR([DocDate]) = 2019 THEN [Quantity] END) AS [2019],

SUM(CASE WHEN YEAR([DocDate]) = 2020 THEN [Quantity] END) AS [2020],

SUM(CASE WHEN YEAR([DocDate]) = 2021 THEN [Quantity] END) AS [2021],

SUM(CASE WHEN YEAR([DocDate]) = 2022 THEN [Quantity] END) AS [2022],

SUM(CASE WHEN YEAR([DocDate]) = 2023 THEN [Quantity] END) AS [2023],

SUM(CASE WHEN YEAR([DocDate]) = 2024 THEN [Quantity] END) AS [2024]

FROM (

SELECT T0.[DocDate], T1.[ItemCode], T1.[Quantity]

FROM OINV AS T0

INNER JOIN INV1 AS T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[ItemCode] LIKE @ItemCode

UNION ALL

SELECT T0.[DocDate], T1.[ItemCode], -T1.[Quantity] AS [Quantity]

FROM ORIN AS T0

INNER JOIN RIN1 AS T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[ItemCode] LIKE @ItemCode

) AS T2

GROUP BY MONTH([DocDate])

ORDER BY [Month];

This query works fine when I hardcode the @ItemCode, however I want to the query manager to get the itemcode from the user and save the itemcode to @ItemCode.

Can someone help please.

[halaiadnan]

Hi - I was able to solve this problem by adding this line into the query.

DECLARE @ItemCode NVARCHAR(MAX) = /*SELECT FROM INV1 T1 WHERE T1.ItemCode = */ '[%0]'

And the query worked.