Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: fetching records based on date

    Hi,

    DB2 v9.1 Z/OS

    Please find the below query

    Code:
    select 
    PLT_NAME,
    FULL_NO,
    LOC_CODE,
    EFF_DATE,
    TARGT_CODE,
    QNTY_RATE,
    VOL_IND
    ,ROW_NUMBER()OVER (PARTITION BY
    PLT_NAME,
    FULL_NO
    ORDER BY EFF_DATE DESC ) AS RN
    
    
    FROM PDP2.BASE_TABLE
    WHERE VOL_IND IN('A','B')
    can you please help me out to get the "EXPECTED RESULT SET" by modifying the query

    Records fillterd by based on the EFF_DATE column and the total of QNTY_RATE should be 100
    we should fetch the record which EFF_DATE close to the current date and the total of QNTY_RATE should be 100
    and future years records upto the year 2020(EFF_date should be current year + 6 year)

    PLT_NAME CHAR(5),
    FULL_NO CHAR(50),
    LOC_CODE CHAR(5),
    EFF_DATE DATE,
    TARGT_CDE CHAR(5),
    QNTY_RATE INTEGER,
    VOL_IND CHAR(1)
    Code:
    SAMPLE RECORDS
    BASE_TABLE
    
    PLT_NAME     FULL_NO   LOC_CODE   EFF_DATE     TARGT_CODE    QNTY_RATE    VOL_IND
    AAAAA        ADE1-RT2 SSSSS      12-03-2011    S1111         50           A
    AAAAA        ADE1-RT2 TTTTT      12-03-2011    S1112         50           A
    AAAAA        ADE1-RT2 SSTTT      30-01-2014    YY111         100          A
    AAAAA        ADE1-RT2 EEEEE      30-01-2014    EEEEE         100          A
    
    AAAAA        E14-HR   XXXXX      10-01-2012    XXXXX         100          A
    AAAAA        E14-HR   YYYYY      11-02-2014    YYYYY         100          A
    AAAAA        E14-HR   YYYYY      28-04-2014    YYYYY         100          A
    
    BBBBB        F11-RT   ABBBB      11-02-2014    ABBB1         100          A
    BBBBB        F11-RT   BBBB1      12-06-2014    BBBB1         0            A
    BBBBB        F11-RT   CCCCC      12-06-2014    CCCCC         100          B
    BBBBB        F11-RT   DDDDD      12-06-2014    DDDDD         0            B
    
    CCCCC        GG-YT    EEE12      21-12-2013    EEEE1         100          B
    CCCCC        GG-YT    DDD12      15-01-2018    DDD12         100          A
    CCCCC        GG-YT    GGG13      12-09-2019    GGG14         100          B
    CCCCC        GG-YT    FTTTT      19-12-2030    HYHHH         100          A
    
    DDDDD        FG-UY    GHHHH      15-09-2013    GHHHH         30           B
    DDDDD        FG-UY    G1111      15-09-2013    G1111         40           A
    DDDDD        FG-UY    T2222      15-09-2013    T2222         70           B
    DDDDD        FG-UY    T3333      15-09-2013    T3333          0           A
    DDDDD        FG-UY    ZZZZZ      24-06-2016    ZZZZZ         100          B
    EXPECTED RESULT SET
    Code:
    PLT_NAME     FULL_NO   LOC_CODE   EFF_DATE     TARGT_CODE    QNTY_RATE    VOL_IND
    AAAAA        ADE1-RT2 SSTTT      30-01-2014    YY111         100          A
    AAAAA        ADE1-RT2 EEEEE      30-01-2014    EEEEE         100          A
    
    AAAAA        E14-HR   YYYYY      11-02-2014    YYYYY         100          A
    AAAAA        E14-HR   YYYYY      28-04-2014    YYYYY         100          A
    
    BBBBB        F11-RT   ABBBB      11-02-2014    ABBB1         100          A
    BBBBB        F11-RT   CCCCC      12-06-2014    CCCCC         100          B
    
    CCCCC        GG-YT    EEE12      21-12-2013    EEEE1         100          B
    CCCCC        GG-YT    DDD12      15-01-2018    DDD12         100          A
    CCCCC        GG-YT    GGG13      12-09-2019    GGG14         100          B
    
    DDDDD        FG-UY    GHHHH      15-09-2013    GHHHH         30           B
    DDDDD        FG-UY    G1111      15-09-2013    G1111         40           A
    DDDDD        FG-UY    T2222      15-09-2013    T2222         70           B
    DDDDD        FG-UY    ZZZZZ      24-06-2016    ZZZZZ         100          B
    Please help..
    Thanks,

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Billa007 View Post
    Hi,

    DB2 v9.1 Z/OS

    Please find the below query

    Code:
    select 
    PLT_NAME,
    FULL_NO,
    LOC_CODE,
    EFF_DATE,
    TARGT_CODE,
    QNTY_RATE,
    VOL_IND
    ,ROW_NUMBER()OVER (PARTITION BY
    PLT_NAME,
    FULL_NO
    ORDER BY EFF_DATE DESC ) AS RN
    
    
    FROM PDP2.BASE_TABLE
    WHERE VOL_IND IN('A','B')
    can you please help me out to get the "EXPECTED RESULT SET" by modifying the query

    Records fillterd by based on the EFF_DATE column and the total of QNTY_RATE should be 100
    we should fetch the record which EFF_DATE close to the current date and the total of QNTY_RATE should be 100
    and future years records upto the year 2020(EFF_date should be current year + 6 year)

    [...]
    Thanks,
    What have you tried, and why did that not work?
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •