Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: fetch the year rows

    Hi;

    Please find the below query and needs to be modified to give a result like in

    "EXPECTED RESULT SET"

    Logic:

    Based on the given CAL_YEAR the query has to fetch from the
    TABLE1 with non-zero MAX_COST rows and with join of TABLE2

    For given year it will return FOUR rows for the same POLICY_NO and POLICY_LOC

    bcoz we need to display in the front-end screen


    for ex:

    if the CAL_YEAR = 2011 means

    fetch from TABLE1 like below
    Code:
    CAL_YEAR POLICY_LOC    POLICY_NO MAX_COST
    
    2011      AAA           1111      4000
    and looking the matching row in TABLE2 with the condition of
    T1.CAL_YEAR=CHAR(YEAR(T2.recv_DT)
    but no rows found,so that ,Fetch the row like which the Previous year
    having RECV_DT and the prescribed AM_COST should be populated for the
    2011 year rows
    In that case...2009 year row only having RECV_DT(10-sep-2009)..so that AM_COST value 500
    should come in Result set


    EXPECTED RESULT SET
    Code:
    IF THE CAL_YEAR = 2011 
    
    
    CAL_YEAR POLICY_LOC    POLICY_NO MAX_COST AM_COST
    
    
    2011      AAA           1111      4000    500
    2011      AAA           1111      4000    500
    2011      AAA           1111      4000    500
    2011      AAA           1111      4000    500
    Code:
    SELECT 
           T1.CAL_YEAR
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T1.MAX_COST
         , T2.AM_COST 
         
     FROM  
    
    (SELECT T1.* FROM
    TABLE1 T1
    
    WHERE T1.MAX_COST > 0)T1 
     LEFT OUTER JOIN
           (SELECT t2.*
                 
                 , ROW_NUMBER()
                      OVER( PARTITION BY POLICY_LOC
                                       , POLICY_NO
                                       , QUARTER(T2.recv_DT)
                                       ,YEAR(T2.recv_DT)
                                ORDER BY RECV_DT DESC NULLS LAST ) AS rn
             FROM  TABLE2) T2
    
    ON
    
    T1.POLICY_LOC=T2.POLICY_LOC
    T1.POLICY_NOC=T2.POLICY_NO
    T1.CAL_YEAR=CHAR(YEAR(T2.recv_DT)
    AND RN=1
    
    WHERE
    
     T1.CAL_YEAR =:WS-YEAR
           
    ;
    Code:
    TABLE1
    
    
    cAL_YEAR POLICY_LOC    POLICY_NO MAX_COST
    
    2010      AAA           1111      3000
    2011      AAA           1111      4000
    2012      AAA           1111      5000
    2013      AAA           1111      3500
    2014      AAA           1111      6500
    2015      AAA           1111       0
    
    
    TABLE2
    
    POLICY_LOC    POLICY_NO   RECV_DT       AM_COST
    
    AAA            1111      10-SEP-2009      500    
    AAA            1111      09-APR-2012     1500
    AAA            1111      25-MAY-2012     1700-->need to fetch this row
    AAA            1111      09-JUL-2014     2500   bcoz of this is latest date row 
                                                    per the same QUARTER
    EXPECTED RESULT SET
    Code:
    IF THE CAL_YEAR = 2011 
    
    
    cAL_YEAR POLICY_LOC    POLICY_NO MAX_COST AM_COST
    
    
    2011      AAA           1111      4000    500
    2011      AAA           1111      4000    500
    2011      AAA           1111      4000    500
    2011      AAA           1111      4000    500
    
    
    IF THE CAL_YEAR = 2012
    
    
    cAL_YEAR POLICY_LOC    POLICY_NO MAX_COST AM_COST
    
    
    2012      AAA           1111      5000     500
    2012      AAA           1111      5000    1700
    2012      AAA           1111      5000    1700
    2012      AAA           1111      5000    1700
    
    IF THE CAL_YEAR = 2013
    
    cAL_YEAR POLICY_LOC    POLICY_NO MAX_COST AM_COST
    
    
    2013      AAA           1111      3500    1700
    2013      AAA           1111      3500    1700
    2013      AAA           1111      3500    1700
    2013      AAA           1111      3500    1700
    
    
    
    
    IF THE CAL_YEAR = 2014
    
    cAL_YEAR POLICY_LOC    POLICY_NO MAX_COST AM_COST
    
    
    2014      AAA           1111      6500    1700
    2014      AAA           1111      6500    1700
    2014      AAA           1111      6500    2500
    2014      AAA           1111      6500    2500
    Please help

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    I don't understand why it will always return 4 rows?
    For given CAL_YEAR = 2011, There is only 1 row matched in table1 and 1 row matched
    "AAA 1111 10-SEP-2009 500 " in table2。

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) If you showed cal_year in your output example differently like in my example,
    it might be easier to understand your requirements.
    OR, I misunderstood your requirements.

    (2) I couldn't understand how QUARTER was related to your output.


    Anyway, here is my trial.

    If you are using DB2 for z/OS(or iSeries), rewrite VALUES caluses by using "SELECT ... FROM sysibm.sysdummy1".

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2010' , 'AAA' , 1111 , 3000 )
    , ( '2011' , 'AAA' , 1111 , 4000 )
    , ( '2012' , 'AAA' , 1111 , 5000 )
    , ( '2013' , 'AAA' , 1111 , 3500 )
    , ( '2014' , 'AAA' , 1111 , 6500 )
    , ( '2015' , 'AAA' , 1111 ,    0 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    VALUES
      ( 'AAA' , 1111 , '10.09.2009' ,  500 )
    , ( 'AAA' , 1111 , '09.04.2012' , 1500 )
    , ( 'AAA' , 1111 , '25.05.2012' , 1700 )
    , ( 'AAA' , 1111 , '09.07.2014' , 2500 )
    )
    SELECT SMALLINT(disp_year) AS cal_year
         , t1.policy_loc
         , t1.policy_no
         , t1.max_cost
         , t2.am_cost
     FROM (VALUES 2011 ) f(ws_year)
     CROSS JOIN
           table1 t1
     CROSS JOIN
           LATERAL
          (VALUES ws_year - 3
                , ws_year - 2
                , ws_year - 1
                , ws_year
          ) f(disp_year)
     INNER JOIN
           LATERAL
          (SELECT am_cost
                , ROW_NUMBER() OVER(ORDER BY recv_year ASC ) AS rnum
            FROM  LATERAL
                 (SELECT t2.* , f.*
                       , ROW_NUMBER()
                            OVER( PARTITION BY recv_year
                                      ORDER BY recv_dt DESC) AS rnum
                       , MIN(recv_year) OVER() AS min_recv_year
                       , MAX(recv_year) OVER() AS max_recv_year
                   FROM  table2 t2
                       , LATERAL
                        (VALUES YEAR(recv_dt) ) AS f(recv_year)
                   WHERE t2.policy_loc = t1.policy_loc
                     AND t2.policy_no  = t1.policy_no
                     AND recv_year    <= ws_year
                 ) t2
            WHERE rnum = 1
              AND
             (    recv_year     >= disp_year
              OR  min_recv_year >  disp_year
              AND min_recv_year =  recv_year
              OR  max_recv_year <  disp_year
              AND max_recv_year =  recv_year
             )
          ) t2
      ON   rnum = 1
     WHERE t1.cal_year = ws_year
       AND t1.max_cost > 0
     ORDER BY
           disp_year
    ;
    ------------------------------------------------------------------------------
    
    CAL_YEAR POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    -------- ---------- ----------- ----------- -----------
        2008 AAA               1111        4000         500
        2009 AAA               1111        4000         500
        2010 AAA               1111        4000         500
        2011 AAA               1111        4000         500
    
      4 record(s) selected.
    other outputs for different ws_year.
    Code:
    /*
    ws_year = 2012
    */
    CAL_YEAR POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    -------- ---------- ----------- ----------- -----------
        2009 AAA               1111        5000         500
        2010 AAA               1111        5000        1700
        2011 AAA               1111        5000        1700
        2012 AAA               1111        5000        1700
    
      4 record(s) selected.
    
    
    /*
    ws_year = 2013
    */
    CAL_YEAR POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    -------- ---------- ----------- ----------- -----------
        2010 AAA               1111        3500        1700
        2011 AAA               1111        3500        1700
        2012 AAA               1111        3500        1700
        2013 AAA               1111        3500        1700
    
      4 record(s) selected.
    
    
    /*
    ws_year = 2014
    */
    CAL_YEAR POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    -------- ---------- ----------- ----------- -----------
        2011 AAA               1111        6500        1700
        2012 AAA               1111        6500        1700
        2013 AAA               1111        6500        2500
        2014 AAA               1111        6500        2500
    
      4 record(s) selected.
    Last edited by tonkuma; 04-06-12 at 05:21. Reason: Changed datatype of table1.cal_year from integer to VARCHAR.

  4. #4
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the replies..

    DB2 ver 9.1 Z/OS
    fengsun2:
    1.There is no '2011 RECV_DT' in the TABLE2,sothat values are taken from
    the previous year rows
    "AAA 1111 10-SEP-2009 500 " means AAA has the value of AM_COST from
    the date of 10-SEP-2009 to next year row "AAA 1111 09-APR-2012 "

    so,,from sep-2009 to 08-APR-2012 the AM_COST value is '500'

    2.We are separated the 4 Quarter rows based on the RECV_DT,The year 2011 does not have the 2011 year date in Table2.Means one row per one Quarter.we are displaying the 4 quarter rows in the front end screen..

    tonkuma:
    Our code does not allow "CROSS JOIN" and "LATERAL" ,we are using

    DB2 v9.1 Z/OS

    Taking the TABLE2 values like below for the clearence
    Code:
     TABLE2
    
    POLICY_LOC    POLICY_NO   RECV_DT       AM_COST
    
    AAA            1111      10-JAN-2013     5500    
    AAA            1111      09-MAR-2013     6500-->need to fetch this row
    AAA            1111      25-AUG-2013     7700   bcoz of this is latest 
    AAA            1111      09-NOV-2013     9500  date row in first Quarter
    Expected Result set
    Code:
    IF THE CAL_YEAR = 2013
    
    CAL_YEAR POLICY_LOC    POLICY_NO MAX_COST AM_COST
    
    
    2013      AAA           1111      3500    6500-->First Quarter -->Taken the Latest date 'AM_COST'row 
    2013      AAA           1111      3500    6500-->Second Quarter->No date in 2nd Quarter,so 'AM_COST'taken from previous Quarter
    2013      AAA           1111      3500    7700-->Third Quater-->Date row availble
    2013      AAA           1111      3500    9500-->Fourth Quater-->Date row availble
    Please let me know,if need any further more clarificatiion

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Please supply CREATE TABLE statements and INSERT statements
    including more data(e.g. another policy_loc, another policy_no, more case for recv_dt, so on...)


    (2) CROSS JOIN and LATERAL can be replaced by INNER JOIN ... ON 0=0 and TABLE.


    (3) I replaced disp_year and recv_year in "Example 1" with ws_year_quarter and recv_year_quarter.

    It would be better to show quarter to clarify your requirements.

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2010' , 'AAA' , 1111 , 3000 )
    , ( '2011' , 'AAA' , 1111 , 4000 )
    , ( '2012' , 'AAA' , 1111 , 5000 )
    , ( '2013' , 'AAA' , 1111 , 3500 )
    , ( '2014' , 'AAA' , 1111 , 6500 )
    , ( '2015' , 'AAA' , 1111 ,    0 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    SELECT policy_loc , policy_no , DATE(recv_dt) , am_cost
    FROM (VALUES
             ( 'AAA' , 1111 , '10.09.2009' ,  500 )
           , ( 'AAA' , 1111 , '09.04.2012' , 1500 )
           , ( 'AAA' , 1111 , '25.05.2012' , 1700 )
           , ( 'AAA' , 1111 , '09.07.2014' , 2500 )
           , ( 'AAA' , 1111 , '10.01.2013' , 5500 )
           , ( 'AAA' , 1111 , '09.03.2013' , 6500 )
           , ( 'AAA' , 1111 , '25.08.2013' , 7700 )
           , ( 'AAA' , 1111 , '09.11.2013' , 9500 )
         ) s(policy_loc , policy_no , recv_dt , am_cost)
    )
    SELECT SMALLINT(ws_year) AS cal_year
         , SUBSTR( CHAR(ws_quarter) , 1 , 1) AS q
         , t1.policy_loc
         , t1.policy_no
         , t1.max_cost
         , t2.am_cost
     FROM (SELECT 2013 FROM sysibm.sysdummy1) f(ws_year)
     INNER JOIN
           table1 t1
      ON   0=0
     INNER JOIN
           TABLE
          (SELECT ws_quarter
                , ws_year * 10 + ws_quarter
            FROM  TABLE
                 (SELECT 1 FROM sysibm.sysdummy1 UNION ALL
                  SELECT 2 FROM sysibm.sysdummy1 UNION ALL
                  SELECT 3 FROM sysibm.sysdummy1 UNION ALL
                  SELECT 4 FROM sysibm.sysdummy1
                 ) f(ws_quarter)
          ) f(ws_quarter , ws_year_quarter)
      ON   0=0
     INNER JOIN
           TABLE
          (SELECT am_cost
                , ROW_NUMBER() OVER(ORDER BY recv_year_quarter ASC ) AS rnum
            FROM  TABLE
                 (SELECT t2.* , f.*
                       , ROW_NUMBER()
                            OVER( PARTITION BY recv_year_quarter
                                      ORDER BY recv_dt DESC
                                ) AS rnum
                       , MIN(recv_year_quarter) OVER() AS min_recv_year_quarter
                       , MAX(recv_year_quarter) OVER() AS max_recv_year_quarter
                   FROM  table2 t2
                       , TABLE
                        (SELECT YEAR(recv_dt) * 10 + QUARTER(recv_dt)
                          FROM  sysibm.sysdummy1
                        ) AS f(recv_year_quarter)
                   WHERE t2.policy_loc = t1.policy_loc
                     AND t2.policy_no  = t1.policy_no
                     AND recv_year_quarter <= ws_year_quarter
                 ) t2
            WHERE rnum = 1
              AND
             (        recv_year_quarter >=   ws_year_quarter
              OR  min_recv_year_quarter >    ws_year_quarter
              AND min_recv_year_quarter =  recv_year_quarter
              OR  max_recv_year_quarter <    ws_year_quarter
              AND max_recv_year_quarter =  recv_year_quarter
             )
          ) t2
      ON   rnum = 1
     WHERE t1.cal_year = ws_year
       AND t1.max_cost > 0
     ORDER BY
           ws_year_quarter
    ;
    ------------------------------------------------------------------------------
    
    CAL_YEAR Q POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    -------- - ---------- ----------- ----------- -----------
        2013 1 AAA               1111        3500        6500
        2013 2 AAA               1111        3500        6500
        2013 3 AAA               1111        3500        7700
        2013 4 AAA               1111        3500        9500
    
      4 record(s) selected.

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Hi;
    Thanks for the reply..
    Code:
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2010' , 'AAA' , 1111 , 3000 )
    , ( '2011' , 'AAA' , 1111 , 4000 )
    , ( '2012' , 'AAA' , 1111 , 5000 )
    , ( '2013' , 'AAA' , 1111 , 3500 )
    , ( '2014' , 'AAA' , 1111 , 6500 )
    , ( '2015' , 'AAA' , 1111 ,    0 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    SELECT policy_loc , policy_no , DATE(recv_dt) , am_cost
    FROM (VALUES
             ( 'AAA' , 1111 , '11.04.2008' ,  400 )
           , ( 'AAA' , 1111 , '10.09.2009' ,  500 ) 
           , ( 'AAA' , 1111 , '09.04.2012' , 1500 )
           , ( 'AAA' , 1111 , '25.05.2012' , 1700 )
           , ( 'AAA' , 1111 , '10.01.2013' , 5500 )
           , ( 'AAA' , 1111 , '09.03.2013' , 6500 )
           , ( 'AAA' , 1111 , '25.08.2013' , 7700 )
           , ( 'AAA' , 1111 , '09.11.2013' , 9500 )
         ) s(policy_loc , policy_no , recv_dt , am_cost)
    )
    
    SELECT 
           T1.CAL_YEAR
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T1.MAX_COST
         , T2.AM_COST 
         
     FROM  
    
    (SELECT T1.* FROM
    TABLE1 T1
    
    WHERE T1.MAX_COST > 0)T1 
     LEFT OUTER JOIN
           (SELECT t2.*
                 
                 , ROW_NUMBER()
                      OVER( PARTITION BY POLICY_LOC
                                       , POLICY_NO
                                       , QUARTER(T2.recv_DT)
                                       ,YEAR(T2.recv_DT)
                                ORDER BY RECV_DT DESC NULLS LAST ) AS rn
             FROM  TABLE2) T2
    
    ON
    
    T1.POLICY_LOC=T2.POLICY_LOC
    T1.POLICY_NOC=T2.POLICY_NO
    T1.CAL_YEAR>=CHAR(YEAR(T2.recv_DT)
    AND RN=1
    Need another set of result sets like below

    Logic :
    Based on the CAL_YEAR the rows shuold be returned from the TABLE2
    "T1.CAL_YEAR>=CHAR(YEAR(T2.recv_DT)"

    It returns all the rows which are less than of CAL_YEAR like below

    ACTUAL RESULT(NOw i am getting)
    Code:
    IF THE CAL_YEAR = 2012
    
    CAL_YEAR  POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    --------  ---------- ----------- ----------- -----------
        2012  AAA             1111      5000       400   
        2012  AAA             1111      5000       500
        2012  AAA             1111      5000      1700
    But i need only one row less than of CAL_YEAR

    Expected Result set
    Code:
    IF THE CAL_YEAR = 2012
    
    
    
    CAL_YEAR  POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    --------  ---------- ----------- ----------- -----------
        2012  AAA               1111        5000         500-->First Quarter doesnot have the value,so taken from previous year value
        2012  AAA               1111        5000        1700-->Second Quarter only has the AM_COST value
    
    IF THE CAL_YEAR = 2013
    CAL_YEAR  POLICY_LOC POLICY_NO   MAX_COST    AM_COST    
    --------  ---------- ----------- ----------- -----------
    
        2013  AAA               1111        3500        6500-->First Quarter value
        2013  AAA               1111        3500        6500-->Reflected First qaurter value,bcoz no 2nd quarter value 
        2013  AAA               1111        3500        7700-->third Quarter value
        2013  AAA               1111        3500        9500-->Fourth Quarter value
    Please help

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    (1) Please supply ...
    including more data(e.g. another policy_loc, another policy_no, more case for recv_dt, so on...)

    ...

    It would be better to show quarter to clarify your requirements.

    ...
    You didn't consider those my requests.
    So, I'll go my way.
    You can go your way. Someone may help you.

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Apology for that..

    Please consider the below query is having more sample data

    Code:
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2011' , 'BBB' , 2222 , 1000 )
    , ( '2012' , 'BBB' , 2222 , 2000 )
    , ( '2013' , 'BBB' , 2222 , 3000 )
    , ( '2012' , 'CCC' , 3333 , 1500 )
    , ( '2013' , 'CCC' , 3333 , 3500 )
    , ( '2012' , 'DDD' , 4444 , 5600 )
    , ( '2013' , 'DDD' , 4444 , 6600 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    SELECT policy_loc , policy_no , DATE(recv_dt) , am_cost
    FROM (VALUES
             ( 'BBB' , 2222 , '21.04.2011' ,  500 )
           , ( 'BBB' , 2222 , '05.05.2012' ,  550 )
           , ( 'BBB' , 2222 , '11.05.2012' ,  600 ) 
           , ( 'BBB' , 2222 , '09.09.2012' , 1500 )
           , ( 'BBB' , 2222 , '25.05.2013' , 1700 )
           , ( 'CCC' , 3333 , '25.05.2009' , 1100 )
           , ( 'CCC' , 3333 , '18.04.2012' , 5500 )
           , ( 'CCC' , 3333 , '09.11.2013' , 6500 )
           , ( 'DDD' , 4444 , '24.06.2010' , 0700 )
           , ( 'DDD' , 4444 , '25.08.2013' , 7700 )
           , ( 'DDD' , 4444 , '19.12.2013' , 9500 )
         ) s(policy_loc , policy_no , recv_dt , am_cost)
    )
    
    SELECT 
           T1.CAL_YEAR
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T1.MAX_COST
         , T2.AM_COST 
         , T2.RECV_DT
         
     FROM  
    
    (SELECT T1.* FROM
    TABLE1 T1
    
    WHERE T1.MAX_COST > 0)T1 
     LEFT OUTER JOIN
           (SELECT t2.*
                 
                 , ROW_NUMBER()
                      OVER( PARTITION BY POLICY_LOC
                                       , POLICY_NO
                                       , QUARTER(T2.recv_DT)
                                       ,YEAR(T2.recv_DT)
                                ORDER BY RECV_DT DESC NULLS LAST ) AS rn
             FROM  TABLE2) T2
    
    ON
    
    T1.POLICY_LOC=T2.POLICY_LOC
    T1.POLICY_NOC=T2.POLICY_NO
    T1.CAL_YEAR>=CHAR(YEAR(T2.recv_DT)
    AND RN=1
    WHERE
    
    T1.CAL_YEAR= :WS-YEAR
    EXPECTED RESULT SET
    Code:
    IF :WS-YEAR=2012
    
    
    
    CAL_YEAR  POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT
    --------  ---------- ----------- ----------- ----------- --------
        2012  BBB             2222      2000       500        21.04.2011
        2012  BBB             2222      2000       600        11.05.2012-->LATEST DATE ROW PER 2ND QUARTER
        2012  BBB             2222      2000      1500        09.09.2012
    
        2012  CCC             3333      1500      1100        25.05.2009
        2012  CCC             3333      1500      5500        18.04.2012
    
    IF :WS-YEAR=2013
    
    CAL_YEAR  POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT
    --------  ---------- ----------- ----------- ----------- --------
        2013  BBB             2222      3000      1500        09.09.2012
        2013  BBB             2222      3000      1700        25.05.2013
    
        2013  CCC             3333      3500      5500        18.04.2012
        2013  CCC             3333      3500      6500        09.11.2013
    
        2013  DDD             4444      6600      0700        24.06.2010
        2013  DDD             4444      6600      7700        25.08.2013
        2013  DDD             4444      6600      9700        19.12.2013
    Please let me know,if need any..

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What result did you got from your query?
    I guessed that you might got more than one past year's rows.

  10. #10
    Join Date
    Sep 2011
    Posts
    220
    yes,,,I got all the past year rows..

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    your guessing is correct..I got the Result set like whatever the past year rows in the table..

    Pleas help me on that how to handle..

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) An idea is...

    (1-1) Put all your query into a subquery and add the following to the SELECT list of the subquery.
    Code:
                , ROW_NUMBER()
                     OVER( ORDER BY recv_dt   DESC ) AS row_num
                , RANK()
                     OVER( ORDER BY recv_year DESC ) AS rank
                , DENSE_RANK()
                     OVER( ORDER BY recv_year DESC ) AS d_rank
    Note(1): Add PARTITION BY clauses.
    Note(2): recv_year is CHAR( YEAR(T2.recv_DT) )


    (1-2) Add WHERE clause for outer SELECT statement, like
    Code:
     WHERE
      (    t2.recv_year = T1.CAL_YEAR
       OR
           t2.d_rank    = 2
       AND t2.row_num   = t2.rank
      )
    Note(2): recv_year is CHAR( YEAR(T2.recv_DT) )


    (2) Another example(rewrite your query).

    Although I thank you to have provided more sample data,
    it is disappointing that you didn't consider the following.
    It would be better to show quarter to clarify your requirements.
    and it is usefull to debug the query, even if quarter was not required in your final result.

    Example 3: I added columns T2_YEAR and Q to sample output(for debugging purpose).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2011' , 'BBB' , 2222 , 1000 )
    , ( '2012' , 'BBB' , 2222 , 2000 )
    , ( '2013' , 'BBB' , 2222 , 3000 )
    , ( '2012' , 'CCC' , 3333 , 1500 )
    , ( '2013' , 'CCC' , 3333 , 3500 )
    , ( '2012' , 'DDD' , 4444 , 5600 )
    , ( '2013' , 'DDD' , 4444 , 6600 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    SELECT policy_loc , policy_no , DATE(recv_dt) , am_cost
    FROM (VALUES
             ( 'BBB' , 2222 , '21.04.2011' ,  500 )
           , ( 'BBB' , 2222 , '05.05.2012' ,  550 )
           , ( 'BBB' , 2222 , '11.05.2012' ,  600 ) 
           , ( 'BBB' , 2222 , '09.09.2012' , 1500 )
           , ( 'BBB' , 2222 , '25.05.2013' , 1700 )
           , ( 'CCC' , 3333 , '25.05.2009' , 1100 )
           , ( 'CCC' , 3333 , '18.04.2012' , 5500 )
           , ( 'CCC' , 3333 , '09.11.2013' , 6500 )
           , ( 'DDD' , 4444 , '24.06.2010' , 0700 )
           , ( 'DDD' , 4444 , '25.08.2013' , 7700 )
           , ( 'DDD' , 4444 , '19.12.2013' , 9500 )
         ) s(policy_loc , policy_no , recv_dt , am_cost)
    )
    SELECT 
           T1.CAL_YEAR
         , SMALLINT( YEAR(T2.recv_DT) ) AS t2_year
         , SUBSTR( CHAR( QUARTER(T2.recv_DT) ) , 1 , 1 ) AS q
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T1.MAX_COST
         , T2.AM_COST 
         , T2.RECV_DT
     FROM  TABLE1 t1
     INNER JOIN
           TABLE
          (SELECT t2.*
                , ROW_NUMBER()
                     OVER( ORDER BY RECV_DT   DESC ) AS row_num
                , RANK()
                     OVER( ORDER BY recv_year DESC ) AS rank
                , DENSE_RANK()
                     OVER( ORDER BY recv_year DESC ) AS d_rank
            FROM  TABLE
                 (SELECT t2.*
                       , ROW_NUMBER()
                            OVER( PARTITION BY QUARTER(T2.recv_DT)
                                             , YEAR   (T2.recv_DT)
                                      ORDER BY RECV_DT DESC NULLS LAST ) AS rn
                   FROM (SELECT t2.*
                              , CHAR( YEAR(T2.recv_DT) ) AS recv_year
                          FROM  TABLE2 t2
                        ) t2
                   WHERE T2.POLICY_LOC =  T1.POLICY_LOC
                     AND T2.POLICY_NO  =  T1.POLICY_NO
                     AND t2.recv_year  <= T1.CAL_YEAR
                 ) T2
            WHERE RN = 1
          ) t2
      ON
      (    t2.recv_year = T1.CAL_YEAR
       OR
           t2.d_rank    = 2
       AND t2.row_num   = t2.rank
      )
     WHERE T1.MAX_COST > 0 
       AND T1.CAL_YEAR = /*:WS-YEAR*/2013
     ORDER BY
           T1.CAL_YEAR
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T2.RECV_DT
    ;
    ------------------------------------------------------------------------------
    
    CAL_YEAR T2_YEAR Q POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT   
    -------- ------- - ---------- ----------- ----------- ----------- ----------
    2013        2012 3 BBB               2222        3000        1500 2012-09-09
    2013        2013 2 BBB               2222        3000        1700 2013-05-25
    2013        2012 2 CCC               3333        3500        5500 2012-04-18
    2013        2013 4 CCC               3333        3500        6500 2013-11-09
    2013        2010 2 DDD               4444        6600         700 2010-06-24
    2013        2013 3 DDD               4444        6600        7700 2013-08-25
    2013        2013 4 DDD               4444        6600        9500 2013-12-19
    
      7 record(s) selected.
    Last edited by tonkuma; 04-10-12 at 08:20.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Billa007,

    your supplied data still too little to debug queries.

    For example, your data not include the case:
    (a) There was no T2 row of specified year(:WS-YEAR), but were multiple past years rows in T2.

    Quote Originally Posted by Billa007 View Post
    Apology for that..

    Please consider the below query is having more sample data

    Code:
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2011' , 'BBB' , 2222 , 1000 )
    , ( '2012' , 'BBB' , 2222 , 2000 )
    , ( '2013' , 'BBB' , 2222 , 3000 )
    , ( '2012' , 'CCC' , 3333 , 1500 )
    , ( '2013' , 'CCC' , 3333 , 3500 )
    , ( '2012' , 'DDD' , 4444 , 5600 )
    , ( '2013' , 'DDD' , 4444 , 6600 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    SELECT policy_loc , policy_no , DATE(recv_dt) , am_cost
    FROM (VALUES
             ( 'BBB' , 2222 , '21.04.2011' ,  500 )
           , ( 'BBB' , 2222 , '05.05.2012' ,  550 )
           , ( 'BBB' , 2222 , '11.05.2012' ,  600 ) 
           , ( 'BBB' , 2222 , '09.09.2012' , 1500 )
           , ( 'BBB' , 2222 , '25.05.2013' , 1700 )
           , ( 'CCC' , 3333 , '25.05.2009' , 1100 )
           , ( 'CCC' , 3333 , '18.04.2012' , 5500 )
           , ( 'CCC' , 3333 , '09.11.2013' , 6500 )
           , ( 'DDD' , 4444 , '24.06.2010' , 0700 )
           , ( 'DDD' , 4444 , '25.08.2013' , 7700 )
           , ( 'DDD' , 4444 , '19.12.2013' , 9500 )
         ) s(policy_loc , policy_no , recv_dt , am_cost)
    )
    ...
    ...
    I added a row to T2, then he query in Example 3 should be updated.

    Example 3a: Bold-Red were added to Example3.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2011' , 'BBB' , 2222 , 1000 )
    , ( '2012' , 'BBB' , 2222 , 2000 )
    , ( '2013' , 'BBB' , 2222 , 3000 )
    , ( '2012' , 'CCC' , 3333 , 1500 )
    , ( '2013' , 'CCC' , 3333 , 3500 )
    , ( '2012' , 'DDD' , 4444 , 5600 )
    , ( '2013' , 'DDD' , 4444 , 6600 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    SELECT policy_loc , policy_no , DATE(recv_dt) , am_cost
    FROM (VALUES
             ( 'BBB' , 2222 , '21.04.2011' ,  500 )
           , ( 'BBB' , 2222 , '05.05.2012' ,  550 )
           , ( 'BBB' , 2222 , '11.05.2012' ,  600 ) 
           , ( 'BBB' , 2222 , '09.09.2012' , 1500 )
           , ( 'BBB' , 2222 , '25.05.2013' , 1700 )
           , ( 'CCC' , 3333 , '25.05.2009' , 1100 )
           , ( 'CCC' , 3333 , '18.04.2012' , 5500 )
           , ( 'CCC' , 3333 , '09.11.2013' , 6500 )
           , ( 'DDD' , 4444 , '25.05.2009' ,  690 ) -- Added
           , ( 'DDD' , 4444 , '24.06.2010' , 0700 )
           , ( 'DDD' , 4444 , '25.08.2013' , 7700 )
           , ( 'DDD' , 4444 , '19.12.2013' , 9500 )
         ) s(policy_loc , policy_no , recv_dt , am_cost)
    )
    SELECT 
           T1.CAL_YEAR
         , SMALLINT( YEAR(T2.recv_DT) ) AS t2_year
         , SUBSTR( CHAR( QUARTER(T2.recv_DT) ) , 1 , 1 ) AS q
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T1.MAX_COST
         , T2.AM_COST 
         , T2.RECV_DT
     FROM  TABLE1 t1
     INNER JOIN
           TABLE
          (SELECT t2.*
                , ROW_NUMBER()
                     OVER( ORDER BY RECV_DT   DESC ) AS row_num
                , RANK()
                     OVER( ORDER BY recv_year DESC ) AS rank
                , DENSE_RANK()
                     OVER( ORDER BY recv_year DESC ) AS d_rank
                , DENSE_RANK()
                     OVER( ORDER BY NULLIF(recv_year , cal_year) DESC NULLS LAST ) AS d_rank_2
            FROM  TABLE
                 (SELECT t2.* , t1.cal_year
                       , ROW_NUMBER()
                            OVER( PARTITION BY QUARTER(T2.recv_DT)
                                             , YEAR   (T2.recv_DT)
                                      ORDER BY RECV_DT DESC NULLS LAST ) AS rn
                   FROM (SELECT t2.*
                              , CHAR( YEAR(T2.recv_DT) ) AS recv_year
                          FROM  TABLE2 t2
                        ) t2
                   WHERE T2.POLICY_LOC =  T1.POLICY_LOC
                     AND T2.POLICY_NO  =  T1.POLICY_NO
                     AND t2.recv_year  <= T1.CAL_YEAR
                 ) T2
            WHERE RN = 1
          ) t2
      ON
      (    t2.recv_year = T1.CAL_YEAR
       OR
           t2.d_rank    = 2
       AND t2.d_rank_2  = 1
       AND t2.row_num   = t2.rank
      )
     WHERE T1.MAX_COST > 0 
       AND T1.CAL_YEAR = /*:WS-YEAR*/2012
     ORDER BY
           T1.CAL_YEAR
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T2.RECV_DT
    ;
    ------------------------------------------------------------------------------
    
    CAL_YEAR T2_YEAR Q POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT   
    -------- ------- - ---------- ----------- ----------- ----------- ----------
    2012        2011 2 BBB               2222        2000         500 2011-04-21
    2012        2012 2 BBB               2222        2000         600 2012-05-11
    2012        2012 3 BBB               2222        2000        1500 2012-09-09
    2012        2009 2 CCC               3333        1500        1100 2009-05-25
    2012        2012 2 CCC               3333        1500        5500 2012-04-18
    
      5 record(s) selected.

    The query of Example 3 for added data returned a exra row.
    Code:
    ...
      ON
      (    t2.recv_year = T1.CAL_YEAR
       OR
           t2.d_rank    = 2
    --   AND t2.d_rank_2  = 1
       AND t2.row_num   = t2.rank
      )
     WHERE T1.MAX_COST > 0 
       AND T1.CAL_YEAR = /*:WS-YEAR*/2012
     ORDER BY
           T1.CAL_YEAR
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T2.RECV_DT
    ;
    ------------------------------------------------------------------------------
    
    CAL_YEAR T2_YEAR Q POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT   
    -------- ------- - ---------- ----------- ----------- ----------- ----------
    2012        2011 2 BBB               2222        2000         500 2011-04-21
    2012        2012 2 BBB               2222        2000         600 2012-05-11
    2012        2012 3 BBB               2222        2000        1500 2012-09-09
    2012        2009 2 CCC               3333        1500        1100 2009-05-25
    2012        2012 2 CCC               3333        1500        5500 2012-04-18
    2012        2009 2 DDD               4444        5600         690 2009-05-25
    
      6 record(s) selected.

  14. #14
    Join Date
    Sep 2011
    Posts
    220
    Thank you very much tonkuma..i really appreciate your effort and timely help..

    I would like to say only one change in the Example3a query
    FROM (VALUES
    ( 'BBB' , 2222 , '21.04.2011' , 500 )
    , ( 'BBB' , 2222 , '05.05.2012' , 550 )
    , ( 'BBB' , 2222 , '11.05.2012' , 600 )
    , ( 'BBB' , 2222 , '09.09.2012' , 1500 )
    , ( 'BBB' , 2222 , '25.05.2013' , 1700 )
    , ( 'CCC' , 3333 , '25.05.2009' , 1100 )
    , ( 'CCC' , 3333 , '18.04.2012' , 5500 )
    , ( 'CCC' , 3333 , '09.11.2013' , 6500 )
    , ( 'DDD' , 4444 , '25.05.2009' , 690 ) -- Added
    , ( 'DDD' , 4444 , '24.06.2010' , 0700 )
    , ( 'DDD' , 4444 , '25.08.2013' , 7700 )
    , ( 'DDD' , 4444 , '19.12.2013' , 9500 )
    ) s(policy_loc , policy_no , recv_dt , am_cost)
    CAL_YEAR T2_YEAR Q POLICY_LOC POLICY_NO MAX_COST AM_COST RECV_DT
    -------- ------- - ---------- ----------- ----------- ----------- ----------
    2012 2011 2 BBB 2222 2000 500 2011-04-21
    2012 2012 2 BBB 2222 2000 600 2012-05-11
    2012 2012 3 BBB 2222 2000 1500 2012-09-09
    2012 2009 2 CCC 3333 1500 1100 2009-05-25
    2012 2012 2 CCC 3333 1500 5500 2012-04-18
    2012 2009 2 DDD 4444 5600 690 2009-05-25
    6 record(s) selected.
    The bolded row should not come in the result set,bcoz it is not a immediate past year row for :WS-year=2012

    instead of the row..below row will come in the result set

    Code:
    CAL_YEAR T2_YEAR Q POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT   
    -------- ------- - ---------- ----------- ----------- ----------- ----------
    2012        2011 2 BBB               2222        2000         500 2011-04-21
    2012        2012 2 BBB               2222        2000         600 2012-05-11
    2012        2012 3 BBB               2222        2000        1500 2012-09-09
    2012        2009 2 CCC               3333        1500        1100 2009-05-25
    2012        2012 2 CCC               3333        1500        5500 2012-04-18
    2012        2010 2 DDD               4444        5600         700 2010-06-24

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    Thank you very much tonkuma..i really appreciate your effort and timely help..

    I would like to say only one change in the Example3a query




    The bolded row should not come in the result set,bcoz it is not a immediate past year row for :WS-year=2012

    instead of the row..below row will come in the result set

    Code:
    CAL_YEAR T2_YEAR Q POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT   
    -------- ------- - ---------- ----------- ----------- ----------- ----------
    2012        2011 2 BBB               2222        2000         500 2011-04-21
    2012        2012 2 BBB               2222        2000         600 2012-05-11
    2012        2012 3 BBB               2222        2000        1500 2012-09-09
    2012        2009 2 CCC               3333        1500        1100 2009-05-25
    2012        2012 2 CCC               3333        1500        5500 2012-04-18
    2012        2010 2 DDD               4444        5600         700 2010-06-24
    The result is not by Example 3a.
    It is the result of Example 3, and Example 3 is not complete, then you should not use it.


    By the way,
    the last result set you showed contradicts the "EXPECTED RESULT SET" you showed first on this post.

    Quote Originally Posted by Billa007 View Post
    Apology for that..

    Please consider the below query is having more sample data

    Code:
    WITH
      TABLE1(cal_year , policy_loc , policy_no , max_cost) AS (
    VALUES
      ( '2011' , 'BBB' , 2222 , 1000 )
    , ( '2012' , 'BBB' , 2222 , 2000 )
    , ( '2013' , 'BBB' , 2222 , 3000 )
    , ( '2012' , 'CCC' , 3333 , 1500 )
    , ( '2013' , 'CCC' , 3333 , 3500 )
    , ( '2012' , 'DDD' , 4444 , 5600 )
    , ( '2013' , 'DDD' , 4444 , 6600 )
    )
    , TABLE2(policy_loc , policy_no , recv_dt , am_cost) AS (
    SELECT policy_loc , policy_no , DATE(recv_dt) , am_cost
    FROM (VALUES
             ( 'BBB' , 2222 , '21.04.2011' ,  500 )
           , ( 'BBB' , 2222 , '05.05.2012' ,  550 )
           , ( 'BBB' , 2222 , '11.05.2012' ,  600 ) 
           , ( 'BBB' , 2222 , '09.09.2012' , 1500 )
           , ( 'BBB' , 2222 , '25.05.2013' , 1700 )
           , ( 'CCC' , 3333 , '25.05.2009' , 1100 )
           , ( 'CCC' , 3333 , '18.04.2012' , 5500 )
           , ( 'CCC' , 3333 , '09.11.2013' , 6500 )
           , ( 'DDD' , 4444 , '24.06.2010' , 0700 )
           , ( 'DDD' , 4444 , '25.08.2013' , 7700 )
           , ( 'DDD' , 4444 , '19.12.2013' , 9500 )
         ) s(policy_loc , policy_no , recv_dt , am_cost)
    )
    
    SELECT 
           T1.CAL_YEAR
         , t1.POLICY_LOC
         , t1.POLICY_NO
         , T1.MAX_COST
         , T2.AM_COST 
         , T2.RECV_DT
         
     FROM  
    
    (SELECT T1.* FROM
    TABLE1 T1
    
    WHERE T1.MAX_COST > 0)T1 
     LEFT OUTER JOIN
           (SELECT t2.*
                 
                 , ROW_NUMBER()
                      OVER( PARTITION BY POLICY_LOC
                                       , POLICY_NO
                                       , QUARTER(T2.recv_DT)
                                       ,YEAR(T2.recv_DT)
                                ORDER BY RECV_DT DESC NULLS LAST ) AS rn
             FROM  TABLE2) T2
    
    ON
    
    T1.POLICY_LOC=T2.POLICY_LOC
    T1.POLICY_NOC=T2.POLICY_NO
    T1.CAL_YEAR>=CHAR(YEAR(T2.recv_DT)
    AND RN=1
    WHERE
    
    T1.CAL_YEAR= :WS-YEAR
    EXPECTED RESULT SET
    Code:
    IF :WS-YEAR=2012
    
    
    
    CAL_YEAR  POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT
    --------  ---------- ----------- ----------- ----------- --------
        2012  BBB             2222      2000       500        21.04.2011
        2012  BBB             2222      2000       600        11.05.2012-->LATEST DATE ROW PER 2ND QUARTER
        2012  BBB             2222      2000      1500        09.09.2012
    
        2012  CCC             3333      1500      1100        25.05.2009
        2012  CCC             3333      1500      5500        18.04.2012
    
    IF :WS-YEAR=2013
    
    CAL_YEAR  POLICY_LOC POLICY_NO   MAX_COST    AM_COST     RECV_DT
    --------  ---------- ----------- ----------- ----------- --------
        2013  BBB             2222      3000      1500        09.09.2012
        2013  BBB             2222      3000      1700        25.05.2013
    
        2013  CCC             3333      3500      5500        18.04.2012
        2013  CCC             3333      3500      6500        09.11.2013
    
        2013  DDD             4444      6600      0700        24.06.2010
        2013  DDD             4444      6600      7700        25.08.2013
        2013  DDD             4444      6600      9700        19.12.2013
    Please let me know,if need any..

Posting Permissions

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