none
Selecting a row of values based on the earliest occurrence of another field RRS feed

  • Question

  • What I am attempting to do is populate A NEW field EarliestDate with the dates from jomast.ftduedate. 

    Fbompart has rows that appear multiple times. While using tempGroup I'm counting each time the Fbompart occurs. I want to take the earliest date (jomast.ftduedate)  from each group and populate the field EarliestDate.


    ftduedate

    fbompart

    tempGroup

    Job

     Earliest date


    1/1/1900

    RTC-21-01032            

    1

    22434-0000

    1/1/1900


    12/1/2018

    RMD-22-01045            

    1

    IT244-0000

    12/1/2018


    12/1/2018

    RMD-22-01045            

    2

    IT242-0000

    12/1/2018


    12/5/2018

    RMD-22-01045            

    3

    IT243-0000

    12/1/2018


    12/4/2018

    IWD-21-01016            

    1

    IS932-0000

    12/4/2018


    12/7/2018

    RMD-22-01012            

    1

    IT199-0000

    12/7/2018


     

    SELECT jomast.fjobno as Job,
    jomast.ftype,
    jomast.fpartno,
    jomast.fpartrev,
    soitem.fcustpart,
    jomast.fac,
    soitem.manualplan,
    inmast.fgroup,
    inmast.fprodcl,
    inmast.fbuyer  ,
    inmast.fnusrqty1,
    inmast.fonhand,
    inmast.fbook,
    inmast.fonhand-inmast.fbook as OverShort,  
    joitem.fdesc,
    jodrtg.foperno,
    jopest.flastoper,
    jomast.fstatus,
    jomast.fcus_id,
    somast.fcontact,
    somast.fsono,
    somast.fcustpono,
    inwork.fdept,
    jodrtg.fpro_id,
    jodrtg.fdescnum,
    jodrtg.fccharcode,
    jomast.fsono,
    joitem.finumber, 
    jodbom.fltooling,
    jodbom.fbompart,
    jomast.fnusrcur1,
    jomast.fdusrdate1,
    jomast.flisapl,
    jomast.ftduedate ,
    jomast.frel_dt,
    jomast.fddue_date,
    sorels.fduedate,
    jomast.fquantity,
    jodrtg.foperqty,
    jodrtg.fnqty_comp,
    jodrtg.fnqty_move,
    jodrtg.fnqty_togo,
    jodrtg.fcomp_date,
    jodrtg.fuprodtime,
    jodrtg.flschedule, 
    joitem.fshipqty,
    sorels.fshipmake,
    row_number() over (Partition by JODBOM.FBOMPART  Order by jomast.ftduedate, JODBOM.FBOMPART, JOMAST.FPARTNO, JOMAST.FDDUE_DATE) as tempGroup
    
     
    FROM jomast 
    LEFT JOIN joitem ON jomast.fjobno=joitem.fjobno 
    LEFT JOIN jopest ON jomast.fjobno=jopest.fjobno 
    LEFT JOIN jodrtg ON jomast.fjobno=jodrtg.fjobno 
    LEFT JOIN jodbom ON jomast.fjobno = jodbom.fjobno and 1=jodbom.fltooling  
    LEFT JOIN somast ON jomast.fsono=somast.fsono 
    LEFT JOIN soitem ON joitem.fsono+joitem.finumber=soitem.fsono+soitem.finumber 
    LEFT JOIN sorels ON joitem.fsono+joitem.finumber+joitem.fkey=sorels.fsono+sorels.finumber+sorels.frelease 
    LEFT JOIN inmast ON jomast.fpartno+jomast.fpartrev=inmast.fpartno+inmast.frev 
    LEFT JOIN inwork ON jodrtg.fpro_id=inwork.fcpro_id
     
    WHERE jomast.flisapl=0 and
    LEFT(jomast.fstatus,1)<>'C' and inwork.fdept='02' AND jodrtg.flschedule=1
    Order by jomast.ftduedate, JODBOM.FBOMPART, JOMAST.FPARTNO, JOMAST.FDDUE_DATE


    • Edited by David9501 Monday, November 26, 2018 7:48 PM
    Monday, November 26, 2018 7:47 PM

Answers