SQL – Select record using MAX over date in 3 fields mm dd yy

41380 pts.
Tags:
AS400 - SQL Queries

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.



Software/Hardware used:
AS400 V7R1

Answer Wiki

Thanks. We'll let you know when a new response is added.

I said UDF, I meant CTE (Common Table Expressions)

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb

    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#

     

    50,840 pointsBadges:
    report
  • philpl1jb

    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

     

     

    50,840 pointsBadges:
    report
  • philpl1jb
    if there were two events on the same day would you want the one with the larger price?
    50,840 pointsBadges:
    report
  • CharlieBrowne

    Philp

    I just could not get your statements to work. Thanks for the effort.

    I ending using an SQL statement with UDF

    With T1 AS                                                       
        (select lppn, max(LPEFYY * 10000 + LPEFMM * 100 + LPEFDD), 0 
           from KBM400MFG/FHLSTPRC a  group by lppn),                
                                                                     
         T2 AS                                                       
      (select PART, EFFDATE, LPBPRC from  macneil/tempprice          
          JOIN KBM400MFG/FHLSTPRC                                    
            on part = lppn                                           
           and EFFDATE = (LPEFYY * 10000 + LPEFMM * 100 + LPEFDD)),  
                                                                     
          T3 AS                                                      
      (Select DISTINCT(PART), LPBPRC from T2)                        
     select * from T3                                                
    41,380 pointsBadges:
    report
  • philpl1jb
    No need to appologize, perhaps some of it was of use. 
    50,840 pointsBadges:
    report
  • TomLiotta

    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

    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following