on 03-26-2009 6:51 AM
Hai all....
I Want query report for Last purchase price for all items, warehouse wise for given date, i tried following query
SELECT
T10.ItemCode,
(Select i1.Itemname from oitm i1 where i1.itemcode = T10.[ItemCode]) as 'ItemName',
T10.[Whscode],
(SELECT T1.[DocDate] FROM OPDN T1 where T1.[DocEntry] = max(T10.[DocEntry])) as 'Doc_Date',
(SELECT T1.[DocNum] FROM OPDN T1 where T1.[DocEntry] = max(T10.[DocEntry])) as 'GRPO_NO',
(SELECT case avg(T2.[Rate]) when 0 then avg(T2.[price]) else (avg(T2.[price]) * isnull(avg(T2.[Rate]),1)) end FROM PDN1 T2 where T2.[DocEntry] = max(T10.[DocEntry])
and T2.Itemcode = T10.[ItemCode]) as 'Unit_Price',
(SELECT isnull(avg(T3.[Rate]),0) FROM PDN1 T3 where T3.[DocEntry] = max(T10.[DocEntry])
and T3.Itemcode = T10.[ItemCode]) as 'CurrencyRate',
(Select isnull(max(OP1.Docnum),0) from OPCH op1 where op1.Docentry in
(SELECT T12.[trgetEntry] FROM PDN1 T12 where T12.[DocEntry] = max(T10.[DocEntry]))) as 'Invoice_No'
FROM PDN1 T10
INNER JOIN OPDN T11 ON T10.DocEntry = T11.DocEntry and T11.[DocType] = 'I'
where T11.Docdate <= '[%0]' and t10.targettype <> 21
GROUP BY
T10.[ItemCode],T10.[Whscode]
this query working good but problem in this query is ,when the user put back dated entry means this report will show wrong value,bcz i write this query based on MAX of docentry please help me to solve this issue
Edited by: Prasanna s on Mar 27, 2009 5:49 AM
Edited by: Prasanna s on Mar 27, 2009 8:00 AM
Hai ...
please help me in this query,my question is not clear or??
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.