I have a file that contains PART#, Price, EFFMM, EFFDD, EFFYY.
There can be multiple records for a PART#.
I want to use SQL to join to this file by PART# and select the PRICE for the record that has the most current date.
Simple in RPG, but I would like to do with SQL.
Discuss This Question: 6  Replies
Assuming that EFFYY , mm, dd are numeric, this would give you the Part# and date with the greatest date.
Select PART#, max( EFFYY * 10000 + EFFMM * 100 + EFFDD)
from myfile group by Part#
Roughly this would do it. But there is an issue if you have two sales in the same day.
select Part#, price from myfile f join (
Select PART#, max( EFFYY * 10000 + EFFMM * 100 + EFFDD) as date1 from myfile group by Part#) d
on f.Part# = d.Part# and d.date1 = f. EFFYY * 10000 + f.EFFMM * 100 + f.EFFDD
Philp
I just could not get your statements to work. Thanks for the effort.
I ending using an SQL statement with UDF
I'd use DIGITS() and CONCAT (and perhaps CHAR() if
EFFDATE
needs it) in order to avoid the arithmetic, but it should work either way.I meant CTE (Common Table Expressions)
What is the purpose of [T1] in your CTE? I don't see how it's referenced anywhere.
Tom