Skip to Content
avatar image
Former Member

Max Item Price

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jun 18, 2010 at 05:49 AM

    Hi Vamsi,

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

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded