I need to complete the column names based on the Usage.Doc_WarehouseApprovedDate in such a way that the column name is in the format [Year_month]
I had a lot of tries but I can’t figure it out how I can do it simply
DECLARE @DateTo#Q@ nvarchar(max) = '2024-03-31'
SELECT
<code here>
CAST(SUM(ISNULL(Usage.[Jan],0)) as INT) as [Jan], -- Zużycie w styczniu
CAST(SUM(ISNULL(Usage.[Feb],0)) as INT) as [Feb], -- Zużycie w lutym
CAST(SUM(ISNULL(Usage.[Mar],0)) as INT) as [Mar], -- Zużycie w marcu
CAST(SUM(ISNULL(Usage.[Apr],0)) as INT) as [Apr], -- Zużycie w kwietniu
CAST(SUM(ISNULL(Usage.[May],0)) as INT) as [May], -- Zużycie w maju
CAST(SUM(ISNULL(Usage.[Jun],0)) as INT) as [Jun], -- Zużycie w czerwcu
CAST(SUM(ISNULL(Usage.[Jul],0)) as INT) as [Jul], -- Zużycie w lipcu
CAST(SUM(ISNULL(Usage.[Aug],0)) as INT) as [Aug], -- Zużycie w sierpniu
CAST(SUM(ISNULL(Usage.[Sep],0)) as INT) as [Sep], -- Zużycie we wrześniu
CAST(SUM(ISNULL(Usage.[Oct],0)) as INT) as [Oct], -- Zużycie w październiku
CAST(SUM(ISNULL(Usage.[Nov],0)) as INT) as [Nov], -- Zużycie w listopadzie
CAST(SUM(ISNULL(Usage.[Dec],0)) as INT) as [Dec] -- Zużycie w grudniu
FROM
Items -- Tabela produktów i części
---- <code here>
LEFT JOIN
(SELECT
Del_ItmID,
Warehouse.DIC_IntColumn1,
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 1 THEN Del_Amount ELSE 0 END) AS [Jan],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 2 THEN Del_Amount ELSE 0 END) AS [Feb],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 3 THEN Del_Amount ELSE 0 END) AS [Mar],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 4 THEN Del_Amount ELSE 0 END) AS [Apr],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 5 THEN Del_Amount ELSE 0 END) AS [May],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 6 THEN Del_Amount ELSE 0 END) AS [Jun],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 7 THEN Del_Amount ELSE 0 END) AS [Jul],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 8 THEN Del_Amount ELSE 0 END) AS [Aug],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 9 THEN Del_Amount ELSE 0 END) AS [Sep],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 10 THEN Del_Amount ELSE 0 END) AS [Oct],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 11 THEN Del_Amount ELSE 0 END) AS [Nov],
SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 12 THEN Del_Amount ELSE 0 END) AS [Dec]
---- <code here>
) as Usage ON Usage.Del_ItmID = Items.Itm_Id
AND Usage.DIC_IntColumn1 = Stock.DIC_IntColumn1 --Pozycje na dokumentach na taki sam produkt
New contributor