March 16, 2015 at 10:06 am
Dear good reference have a consultation with relation to the function of aggregation, in reality PIVOT look
SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName],
(ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
FROM OINM M
INNER JOIN OITW T0 ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
WHERE M.DocDate <= getdate()
GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName
ORDER BY T0.[WhsCode], T0.[ItemCode]
I want to convert the PIVOT , doing this
Select WhsCode, [100] Sh,[101] TI, [102] Bod, [103], [104] PR, [199] CO from (
SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName]
--(ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
FROM OINM M
INNER JOIN OITW T0 ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
WHERE M.DocDate <= getdate())
V PIVOT ( (ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) FOR WhsCode )
--GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName
--ORDER BY T0.[WhsCode], T0.[ItemCode]
but sends error in the function of aggregation :
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '('.
March 16, 2015 at 5:54 pm
*check this brings results but can not find how to group them by WhsName*/
DECLARE @DATE AS DATETIME;
SET @DATE = GETDATE ();
select P.ItemName, P.ItemCode,P.WhsName,
[100] Sh,
[101] TI,
[102] Bod,
[103] ST,
[104] PR,
[199] CO
from (
SELECT T0.[WhsCode],T2.[WhsName], T0.[ItemCode], T1.[ItemName],
(ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
FROM OINM M WITH (NOLOCK)
INNER JOIN OITW T0 (NOLOCK) ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
INNER JOIN OITM T1 (NOLOCK) ON T0.ItemCode = T1.ItemCode
INNER JOIN OWHS T2 (NOLOCK) ON T0.WhsCode = T2.WhsCode
WHERE M.DocDate <= @DATE AND M.ItemCode = '05031-101'
GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName
) P
PIVOT (
SUM(Stock)
FOR [WhsCode] IN ([100],[101],[102],[103],[104],[199])
) P
ORDER BY P.ItemName, P.ItemCode
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply