cancel
Showing results for 
Search instead for 
Did you mean: 

Stock Value

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this one:

SELECT T2.CardName, T1.ItemCode, sum(T0.OnHand* T0.AvgPrice) StckValue

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\]'

AND T0.Onhand > 0

GROUP BY T2.CardName, T1.ItemCode

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Thanks for your swift response. I finally managed to link the two queries in my thread thanks to UNION as shown below:

select cardname,sum(val) val, sum(sales) sales, sum(GP) GP

from

(

SELECT T2.[CardName],0 val,sum(T1.GTotal) Sales,sum(T1.GrssProfit) GP FROM OITM T0 INNER JOIN INV1 T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode where T1.Docdate >=[%0] and T1.DocDate <=[%1]

group by T2.CardName

union all

SELECT v2.Cardname,sum(v0.OnHand*v0.AvgPrice) val,0 sales,0 GP FROM OITW v0 INNER JOIN OITM v1 ON v0.ItemCode = v1.ItemCode

left outer JOIN OCRD v2 ON v1.CardCode = v2.CardCode where v2.frozenfor='n' and v2.Balance <>0 group by v2.Cardname

)v

group by v.cardname

Seems to work fine.

Rgds,

Henry

Answers (0)