MAX(somedate) help 1

[muntz70]

I've been trying to generate a table using MAX(somedate). My goal is to pull one row of data from (storeitems) that is JOINed to another table that has the prices. However, the (price) table has more than one listing for the price. I want the most current price using the (effectivedate) field.

Here's my statement so far:

SELECT its.storeid,OnHandQty,(OnHandQty*rv.price) rtvalue,rv.price,its.itemid,(mcl.descr)subcat,(its.descr)itemdescr,mcl.parentclass
FROM StoreItem its
JOIN (SELECT itemid,price,MAX(effectivedttm) as currdate FROM EffectiveStoreItemRetailValue GROUP BY itemid,price)rv on its.itemid=rv.itemid
JOIN MdseClass mcl on its.classid=mcl.classid
WHERE its.storeid= 50
AND OnHandQty <> 0
GROUP BY its.storeid,OnHandQty,rtvalue,its.itemid,subcat,itemdescr,mcl.parentclass,rv.price
ORDER BY mcl.parentclass;

I'm doing some other things in this statement, however, my main concern is why the MAX(somedate) is not working. I have a feeling it has to do with how I'm joining the tables..

Thanks in advance folks!
Tim

 

[nikhilparchure]

The SQL is wrong from its first look
What you are achieveing by the above is

Grouping by items -- > price --> get max effective date
for that item-price combination which is not what you require

Well I did some sample test as see the need of modifying
your derived table query with the below

Select A.ItemsID , A.Price , A.effectiveDate
FROM Test A
INNER JOIN
(
Select ItemsId , MAX(effectivedate) effectivedate
From Test
Group By ItemsId
) B
ON
A.ItemsID = B.ItemsID AND
A. EffectiveDate = B.EffectiveDate


Try this and lemme know if this solves your problem
I am assuming that there is just one price per effective day.

 

[muntz70]

That still does not work. I guess I'm applying your suggestion wrong....

What you listed is inside () of the first JOIN ?

below was another method I was trying... and still no luck

SELECT its.itemid, its.storeid,
its.descr, its.onhandqty,
pt.price, pt.Effectivedttm,
(cl.descr)classDescr
FROM StoreItem its
INNER JOIN EffectiveStoreItemPriceHistory PT ON its.itemid=PT.itemid
INNER JOIN MdseClass cl ON its.classid=cl.classid
WHERE its.storeid=50
AND its.onhandqty<>0
AND PT.Effectivedttm=(SELECT TOP 1 Effectivedttm
FROM EffectiveStoreItemPriceHistory
WHERE PT.itemid=itemid
AND effectivedttm <= now())


any thoughts?

 

[nikhilparchure]

Is this what you are trying to do!!!

SELECT its.storeid,OnHandQty,(OnHandQty*rv.price) rtvalue,rv.price,its.itemid,(mcl.descr)subcat,(its.descr)itemdescr,mcl.parentclass
FROM StoreItem its
JOIN
(

Select A.itemid , A.Price , A.effectiveDate as currdate
FROM EffectiveStoreItemRetailValue A
INNER JOIN
(
Select itemid , MAX(effectivedate) effectivedate
From EffectiveStoreItemRetailValue
Group By itemid
) B
ON
A.itemid = B.itemid AND
A. EffectiveDate = B.EffectiveDate


)rv
on its.itemid=rv.itemid
JOIN MdseClass mcl on its.classid=mcl.classid
WHERE its.storeid= 50
AND OnHandQty <> 0
GROUP BY its.storeid,OnHandQty,rtvalue,its.itemid,subcat,itemdescr,mcl.parentclass,rv.price
ORDER BY mcl.parentclass

let me know the results and a short explaination about the output

 

[muntz70]

That worked great. I didn't have all my ()'s in the correct places...

Thanks a bunch!