Hola. Tengo esta consulta con PIVOT que me da correctamente los resultados:
WITH Previsión (MES, CÓDIGO, ARTÍCULO, DELEGACIÓN, KILOS) AS
(SELECT DATENAME(dw,T0.[Docdate]) as 'MES', T1.[ItemCode] AS 'CÓDIGO', T1.[Dscription] AS 'ARTÍCULO', T1.[WhsCode] AS 'DELEGACIÓN', sum(T1.[Quantity]) AS 'KILOS' FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] and T0.[DocType] <> 'S' AND T1.[ItemCode] Like '3%%' AND (T1.[WhsCode] = '02' OR T1.[WhsCode] = '37') AND (T1.[BaseType] ='-1' or T1.[BaseType] ='17') GROUP BY T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode]
union all
SELECT DATENAME(dw,T0.[Docdate]) as 'MES', T1.[ItemCode] AS 'CÓDIGO', T1.[Dscription] AS 'ARTÍCULO', T1.[WhsCode] AS 'DELEGACIÓN', sum(T1.[Quantity]) AS 'KILOS' FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] and T0.[DocType] <> 'S' AND T1.[ItemCode] Like '3%%' AND (T1.[WhsCode] = '02' OR T1.[WhsCode] = '37') GROUP BY T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode]
union all
SELECT DATENAME(dw,T0.[Docdate]) as 'MES', T1.[ItemCode] AS 'CÓDIGO', T1.[Dscription] AS 'ARTÍCULO', T1.[WhsCode] AS 'DELEGACIÓN', sum(T1.[Quantity])*-1 AS 'KILOS' FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] and T0.[DocType] <> 'S' AND T1.[ItemCode] Like '3%%' AND (T1.[WhsCode] = '02' OR T1.[WhsCode] = '37') AND (T1.[BaseType] = '-1' OR T1.[BaseType] = '13') GROUP BY T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode]
union all
SELECT DATENAME(dw,T0.[Docdate]) as 'MES', T1.[ItemCode] AS 'CÓDIGO', T1.[Dscription] AS 'ARTÍCULO', T1.[WhsCode] AS 'DELEGACIÓN', sum(T1.[Quantity])*-1 AS 'KILOS' FROM ORDN T0 INNER JOIN RDN1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] and T0.[DocType] <> 'S' AND T1.[ItemCode] Like '3%%' AND (T1.[WhsCode] = '02' OR T1.[WhsCode] = '37') GROUP BY T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[WhsCode])
Select * from Previsión
pivot (sum(KILOS) for MES in
([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])) PVT
Lo que quiero lograr es obtener una última columna donde aparezca el resultado de cada fila al final con la suma de los datos de la semana para cada "artículo"
Alguien podría ayudarme. Gracias.