Max Item Price

[Former Member]

Hi Guys,

I need some changes in the below query.

the query is showing correct values for opening, issue , receipt and closing stock values but the problem arising in the price calculation.

In the query the item price is calculated from max(price) or Avg(Price) but we need the item price for that particular transaction.

Right now we are maintaining FIFO in item price and just we need the price for each opening, issue,receipt and closing stock individually at what item price that transaction has been done.

select a.ItemCode,a.Des, a.WareHouse , (SELECT (sum(t1.inqty - t1.outqty)) 
FROM OINM t1  
WHERE (T1.DocDate < '[%1]') and (t1.itemcode = a.itemcode) and (t1.warehouse = a.whs) ) as 'OB', a.Price,a.inqty as 'Goods Receipt',a.outqty as 'Goods Issue', (SELECT (sum(t1.inqty) - sum(t1.outqty)) 
FROM OINM t1   
WHERE (T1.DocDate <= '[%2]') and (t1.itemcode = a.itemcode) and (t1.warehouse = a.whs)) as 'Closing',
( a.Price *(SELECT (sum(t1.inqty) - sum(t1.outqty)) 
FROM OINM t1   
WHERE (T1.DocDate <= '[%2]') and (t1.itemcode = a.itemcode) and (t1.warehouse = a.whs)) ) as 'Closing Value'
from (SELECT max(T0.[ItemCode]) as 'ItemCode', max(T0.[Dscription]) as 'Des',sum(T0.[InQty]) as 'InQty', sum(T0.[OutQty]) 'OutQty',t0.warehouse as 'whs' , 
(select w1.whsname from owhs w1 where w1.whscode = t0.warehouse) as 'Warehouse',max(T0.[Price]) as 'Price'
FROM OINM T0  
inner join oitm o1 on o1.itemcode = t0.itemcode 
inner join oitb o2 on o2.itmsgrpcod = o1.itmsgrpcod  
WHERE (T0.[DocDate] >= '[%1]' and  T0.[DocDate] <= '[%2]')  and 
(o2.itmsgrpnam >= '[%3]' and o2.itmsgrpnam <= '[%4]' ) 
group by T0.[ItemCode],t0.warehouse)a

Regards,

Vamsi

[Former Member · Accepted solution]

Hi Vamsi,

You may refer to my answer from your another similar thread.

Thanks,

Gordon