Max(somedate) help please 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

 

[LFCfan]

The query below, or something very similar, should work:

SELECT its.storeid,OnHandQty,(OnHandQty * rv.price) rtvalue, rv.price,its.itemid,(mcl.descr)subcat,(its.descr)itemdescr,mcl.parentclass
FROM StoreItem its
JOIN [green]--(SELECT itemid,price,MAX(effectivedttm) as currdate FROM EffectiveStoreItemRetailValue GROUP BY itemid,price)rv [/green]
[red]
EffectiveStoreItemRetailValue rv
[/red]
on its.itemid=rv.itemid
JOIN MdseClass mcl on its.classid=mcl.classid
WHERE its.storeid= 50
AND OnHandQty <> 0
[red]and rv.effectivedttm = (select top 1 effectivedttm from EffectiveStoreItemRetailValue where itemid = rv.itemid order by effectivedttm desc)[/red]
GROUP BY its.storeid,OnHandQty,rtvalue,its.itemid,subcat,itemdescr,mcl.parentclass,rv.price
ORDER BY mcl.parentclass

 

[maswien]

The max(effectivedttm) isn't actually used in the other part of the query, so the query can be simplified to:


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 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;

 

[donutman]

The reason your not getting what you want is that all Aggregate function work on a group of rows identified by the Group By clause. Each item in that clause helps define the group. So when you include the price in the Group By clause, you are making a group out of every price rather than actually getting the price for a particular row, namely the row with the most current date.
The join must be done in such a way that you can identify exactly which row happens to have the effectivedttm = MAX(effectivedttm). To do that you must create a derived table of all the MAX(effectivedttm) dates for each product. So the grouping is only on ItemID (and maybe StoreID if each store has its own pricing).
I see that LFCfan has just posted a solution, so I'll just leave it at this explanation.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[muntz70]

Thanks for all the input folks. I still must be missing something. Now it doesn't return anything.

I understand my problem in the "group by" from before. I removed the [rtvalue] from there.

I *think* I did what LFCfan suggested, but seems I'm still stuck. I'm not new to SQL (not that I'm an expert either), so this is really frustrating me.

Below is the "updated" statement... any clues?


SELECT its.storeid,OnHandQty,(OnHandQty * rv.price) rtvalue, rv.price,its.itemid,(mcl.descr)subcat,(its.descr)itemdescr,mcl.parentclass
FROM StoreItem its
JOIN EffectiveStoreItemRetailValue rv on its.itemid=rv.itemid
JOIN MdseClass mcl on its.classid=mcl.classid
WHERE its.storeid= 50
AND OnHandQty <> 0
and rv.effectivedttm = (select top 1 effectivedttm from EffectiveStoreItemRetailValue where itemid = rv.itemid order by effectivedttm)
GROUP BY its.storeid,OnHandQty,its.itemid,subcat,itemdescr,mcl.parentclass,rv.price
ORDER BY mcl.parentclass

thanks for your patience

 

[maswien]

Remove the filter its.storeid= 50, and see if you can get answers, I guess there is no match for it in the other table.