Link to home
Start Free TrialLog in
Avatar of valmatic
valmaticFlag for United States of America

asked on

how would I display all item and price information where an item's most current effective date?

Hi, I'm trying to write an IBMi DB2 SQL select statement to display records for the max(effectiveDate).  That works fine until I pull in my price column.  Then all records are returned.  IItem\Price are key values.  How can I pull in the single record by most current effective date


Here is a dataset for the example part in my query.  This is one item out of roughly  70000 distinct parts.  Ultimately, I want to grab the record for each item with the most current effective date.

ItemPriceEffDate
1806ABN.3XF3,599.00500101
1806ABN.3XF2,243.001170501
1806ABN.3XF2,658.001181001
1806ABN.3XF2,812.001190601
1806ABN.3XF2,944.001191101
1806ABN.3XF3,020.001210101
1806ABN.3XF3,363.001210701
1806ABN.3XF3,599.001220501


the code below returns:   

ItemEffDate
1806ABN.3XF1220501

  

select itnbr Item, max(bzbldt) EffDate 

from Library.ITRBPR 

where itnbr = '1806ABN.3XF'

group by itnbr;


If I add the price column into my query, I get all records..  

select itnbr Item, bzanva Price ,max(bzbldt) EffDate 

from Library.ITRBPR 

where itnbr = '1806ABN.3XF'

group by itnbr, bzanva;


Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Howdy valmatic,


There are several ways to get here.  I'd write the query something like this:


select itnbr Item, bzanva Price, EffDate 

from Library.ITRBPR t0

where t0.EffDate = (SELECT max(EffDate) from Library.ITRBPR t1 where t0.itnbr = t1.itnbr)

--  and itnbr = '1806ABN.3XF'

group by itnbr, bzanva;


That will select the row with the most recent effective date for each product.

Avatar of valmatic

ASKER

Hi Kent,  Thanks for the quick response!!  I got hit with SQL duplicate value errors when I ran that query.  I did get some results before it bombed out however.  Maybe a problem in the data or maybe the SQL client I'm using.  I'm using the IBMi ACS SQL script tool.    

Is there another way to tackle this?, or I'll see if I can debug. ..  I'll play around with it more this weekend..
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks both of you.  
Kent, I think I would have gotten there but I think the IBM ACS has some proprietary factors I'd need to account for.  
Slight, thanks much.  Didn't really think about handling in that way but it's in my hip pocket now..