on 03-23-2009 7:35 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
89 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.