Am working on Query1 below and everything seems ok apart from the StckValue: sum(t3.Onhand*t3.avgprice) StckValue
The Amount for Stock Value is giving out very high and unrealistic figures compared to when I query for the same but using query number 2 below. I however want to display all the correct details in one query as attempted in Query1.
sum(T1.[StockPrice]* T1.[Quantity]) Cost,
(sum(T1.LineTotal)-sum(T1.[StockPrice]* T1.[Quantity])) GP,
(((sum(T1.LineTotal)-sum(T1.[StockPrice]* T1.[Quantity]))/sum(LineTotal))*100) [G.P%]
FROM OITM T0
INNER JOIN INV1 T1
ON T0.ItemCode = T1.ItemCode
INNER JOIN OCRD T2
ON T0.CardCode = T2.CardCode
inner join oitw t3
where t3.whscode='%0' t1.whscode='%0'
and T1.Docdate >='%1' and T1.DocDate <='%2'
GROUP BY T2.[CardCode], T2.[CardName]
SELECT T2.CardName, sum(T0.OnHand* T0.AvgPrice) FROM OITW T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode left outer JOIN OCRD T2 ON T1.CardCode = T2.CardCode WHERE T0.WhsCode ='%0' GROUP BY T2.CardName
Will appreciate very much if someone sorts me out the soonest possible. Thanks.