Query for Item Average Price for specific date range

[Former Member]

Experts, I need your help. I have been tasked to come up with a query that would allow my end user to select a item code and a date range then return the AVG price for that date range. I see how I can get the AVG price from OITW for right now, but how is it calculated for a previous time period?

Thanks!

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[ItemCode], T0.[Dscription], T0.[Warehouse], T0.[CalcPrice] FROM OINM T0 WHERE T0.[ItemCode] = [%0] and  T0.[DocDate] between [%1] and [%2] and  T0.[Warehouse]  = [%3]

Thanks & Regards,

Nagarajan

[former_member861881 · Accepted solution]

Hi sir

The above query is perfect but our management asking with minimum price and maximum price within the warehouses with separate column Like shown below: is it possible

SELECT T0.[ItemCode], T0.[Dscription], T0.[Warehouse], MIN(T0.[CalcPrice]), MAX(T0.CalcPrice) FROM OINM T0 WHERE T0.[ItemCode] = [%0] and T0.[DocDate] between [%1] and [%2] and T0.[Warehouse] = [%3]

[former_member1079264]

both answers are not correct

[former_member683097]

mmm.... nou... i see that Manish´s neither it is correct answer to Former member Question... it seems that is more complex that it looks like....

[former_member683097]

Hi Dears... you marked the Nagarajan Query like correct answer, but.... it is not the Manish's Query the correct !!??...

[former_member184146]

HI,

Try this

SELECT T0.[ItemCode], T0.[Dscription], T0.[Warehouse], AVG(T0.[CalcPrice]) as [Avg Price] FROM OINM T0

WHERE T0.[ItemCode] = [%0] and  T0.[DocDate] between [%1] and [%2] and  T0.[Warehouse]  = [%3]

GROUP BY t0.ItemCode,t0.Dscription,t0.Warehouse

ORDER BY t0.ItemCode

--Manish