Select
ItemCode =S.Code,
ItemDescription1 =S.Description_1,
ItemDescription2 =S.Description_2,
ItemGroupCode =G.StGroup,
ItemGroupDesc = G.Description,
W.Code as WHCode,
Qty =
SUM(CASE
WHEN Debit > 0 THEN Quantity
ELSE -Quantity
END)
--,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
,SV = SUM(CASE
WHEN P.Id NOT IN ('OInv', 'Crn') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
WHEN Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
ELSE 0 END)
, SVAvgCost = CASE WHEN SUM(CASE WHEN Debit > 0 THEN Quantity ELSE -Quantity END) <> 0 THEN
SUM(CASE
WHEN P.Id NOT IN ('OInv', 'Crn') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
WHEN Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
ELSE 0 END) /
SUM(CASE WHEN Debit > 0 THEN Quantity ELSE -Quantity END)
ELSE 0 END
, WS.fAverageCost as WHAvgCost
from PostST P
LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
LEFT JOIN WhseStk WS ON P.WarehouseID = WS.WHWhseID AND P.AccountLink = WS.WHStockLink
WHERE P.TxDate <='2018-12-31'
AND S.ServiceItem = 0
AND S.StockLink = 53
GROUP BY
S.Code,
W.Code,
S.Description_1,
WS.fAverageCost,
S.Description_2,
S.ItemGroup,
G.StGroup,
G.Description
Order by S.Code |