Last Purchase price Warehouse wise for given date

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