0
Former Member
Mar 23, 2009 at 07:35 AM

# Stock Value

160 Views

Hi all,

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.

Query 1:

SELECT T2.[CardCode],

T2.[CardName],

sum(T1.[StockPrice]* T1.[Quantity]) Cost,

sum(t3.Onhand*t3.avgprice) StckValue,

sum(T1.Linetotal) Sales,

(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

on t0.itemcode=t3.itemcode

where t3.whscode='%0' t1.whscode='%0'

and T1.Docdate >='%1' and T1.DocDate <='%2'

GROUP BY T2.[CardCode], T2.[CardName]

Query 2:

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.

Rgds,

Henry