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

    Unanswered: select query should fetch latest date calculation

    Hi;
    please find the below query
    Code:
    SELECT 
    POLICY_LOC
    ,POLICY_NO
    ,Q1
    ,Q2
    ,Q3
    ,Q4                    
    ,RECV_DT    
    
    
    FROM( 
    
    SELECT                                  
    T1.POLICY_LOC
    ,T1.POLICY_NO
         
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (01,02,03))
                  THEN T1.AM_COST-T2.AM_COST
                   ELSE T1.AM_COST
                   END Q1
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (04,05,06))
                  THEN T1.AM_COST-T2.AM_COST
                   ELSE T1.AM_COST
                   END Q2
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (07,08,09))
                  THEN T1.AM_COST-T2.AM_COST
                   ELSE T1.AM_COST
                   END Q3
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (10,11,12))
                  THEN T1.AM_COST-T2.AM_COST
                   ELSE T1.AM_COST
                   END Q4
    ,T2.RECV_DT
       
    ,ROW_NUMBER()   
    
    OVER(PARTITION BY T1.POLICY_LOC, T1.POLICY_NO
                            ORDER BY T2.RECV_D DESC NULLS LAST) AS rn
                                    
                            
     FROM   TABLE1 T1      
    
    LEFT OUTER JOIN
    
    TABLE2   T2 
    ON
    T1.POLICY_LOC=T2.POLICY_LOC
    T1.POLICY_NO=T2.POLICY_NO
    WHERE   
    
        T1.C_YEAR='2012'
    
     )T  
    
     WHERE 
    
       rn = 1
    ;
    If the SAME policy_no has multiple month(RECV_DT) rows in the same quarter means..Need to select the
    latest date row and do the above calculation
    Table1
    Code:
    POLICY_LOC POLICY_NO      AM_COST           
    
    AAA        1234            250     
    BBB        3333            500      
    CCC        1234            050
    Table2

    Code:
    POLICY_LOC  POLICY_NO          AM_COST RECV_DT        
    
    AAA         1234                100    2012-03-13  
    AAA         1234                200    2012-04-12 
    AAA         1234                700    2013-04-11-->no need to fetch,bcoz given C_YEAR is 2012
    
    BBB         3333                100    2012-09-16 
    BBB         3333                600    2012-09-29-->NEED TO FETCH LATEST ROW
    BBB         3333                100    2012-12-22
    EXPECTED RESULT
    Code:
    POLICY_LOC   POLICY_NO      Q1       Q2      Q3      Q4     RECV_DT        
    
    AAA         1234            150      0       0       0        2012-03-13  
    AAA         1234            0       50       0       0        2012-04-12 
    
    BBB         3333            0        0      -100     0        2012-09-29 
    BBB         3333            0        0       0      400       2012-12-22
    Please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ...
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (01,02,03))
                  THEN T1.AM_COST-T2.AM_COST
                   ELSE T1.AM_COST
                   END Q1
    ...
    The CASE expression might be re-written by using QUARTER function, like

    Code:
    /* Example 1a: */
    , CASE QUARTER(T2.RECV_DT)
      WHEN 1 THEN T1.AM_COST - T2.AM_COST
      ELSE        T1.AM_COST
      END  Q1
    
    /* Example 1b: */
    , T1.AM_COST
      - CASE QUARTER(T2.RECV_DT)
        WHEN 1 THEN T2.AM_COST
        ELSE        0
        END  Q1
    
    /* Example 1c: */
    , T1.AM_COST
      - INT(COS( QUARTER(T2.RECV_DT) - 1 )) * T2.AM_COST Q1
    Note: I don't know your CASE expression meets your requirement.
    Last edited by tonkuma; 01-07-12 at 11:01. Reason: Add Note.

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    There are a few questions about your query ?
    1, What is the column C_YEAR , is it T2.RECV_DT ?
    2 If you want to discard the row "CCC" in table1 which is not in table2
    Why using the left out join ,not inner join?
    3, As tonkuma metioned above , I think the case expression is not what you want......
    Any way , you can add QUARTER function to the partition by clause to pick up the
    last day of the quarter. Some thing like this :

    with table1( policy_loc,policy_no,am_cost ) as
    ( values ('AAA',1234, 250),
    ('BBB',3333, 500),
    ('CCC',1234, 050 ) )
    ,table2 ( policy_loc,policy_no,am_cost,recv_dt ) as
    ( values ('AAA',1234, 100, '2012-03-13'),
    ('AAA',1234, 200, '2012-04-12'),
    ('AAA',1234, 700, '2013-04-11'),
    ('BBB',3333, 100, '2012-09-16'),
    ('BBB',3333, 600, '2012-09-29'),
    ('BBB',3333, 100, '2012-12-22') )
    SELECT
    POLICY_LOC
    ,POLICY_NO
    ,Q1
    ,Q2
    ,Q3
    ,Q4
    ,RECV_DT
    FROM(
    SELECT
    T1.POLICY_LOC
    ,T1.POLICY_NO
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (01,02,03))
    THEN T1.AM_COST-T2.AM_COST
    ELSE 0
    END Q1
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (04,05,06))
    THEN T1.AM_COST-T2.AM_COST
    ELSE 0
    END Q2
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (07,08,09))
    THEN T1.AM_COST-T2.AM_COST
    ELSE 0
    END Q3
    ,CASE WHEN( MONTH(T2.RECV_DT) IN (10,11,12))
    THEN T1.AM_COST-T2.AM_COST
    ELSE 0
    END Q4
    ,T2.RECV_DT
    ,ROW_NUMBER()
    OVER(PARTITION BY T1.POLICY_LOC, T1.POLICY_NO, QUARTER(T2.RECV_DT)
    ORDER BY T2.RECV_DT DESC NULLS LAST) AS rn
    FROM TABLE1 T1
    inner JOIN
    TABLE2 T2
    ON T1.POLICY_LOC=T2.POLICY_LOC
    and T1.POLICY_NO=T2.POLICY_NO
    Where Year(T2.RECV_DT) = 2012
    ) T
    WHERE rn = 1;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you put table2 in a subquery with ROW_NUMBER and "WHERE Year(T2.RECV_DT) = 2012" then join the subquery with table1,
    you can remove outermost select and put "rn = 1" in ON condition.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    If you put table2 in a subquery with ROW_NUMBER and "WHERE Year(T2.RECV_DT) = 2012" then join the subquery with table1,
    you can remove outermost select and put "rn = 1" in ON condition.
    Here is an example.
    Additional to the design plan, it also removed repeating expressions.
    Code:
    SELECT t1.POLICY_LOC
         , t1.POLICY_NO
         , INT(COS(recv_qrtr - 1)) * cost_diff Q1
         , INT(COS(recv_qrtr - 2)) * cost_diff Q2
         , INT(COS(recv_qrtr - 3)) * cost_diff Q3
         , INT(COS(recv_qrtr - 4)) * cost_diff Q4
         , t2.RECV_DT
     FROM  TABLE1 T1
     INNER JOIN
           (SELECT t2.*
                 , recv_qrtr
                 , ROW_NUMBER()
                      OVER( PARTITION BY POLICY_LOC
                                       , POLICY_NO
                                       , recv_qrtr
                                ORDER BY RECV_DT DESC ) AS rn
             FROM  TABLE2 T2
             CROSS JOIN LATERAL
                   (VALUES QUARTER(T2.RECV_DT) ) f(recv_qrtr)
             WHERE YEAR(RECV_DT) = '2012'
           ) t2
       ON  T2.POLICY_LOC = T1.POLICY_LOC
       AND T2.POLICY_NO  = T1.POLICY_NO
       AND rn = 1
     CROSS JOIN LATERAL
           (VALUES T1.AM_COST - T2.AM_COST ) f(cost_diff)
    ;

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Thanks for the reply..I ran the below query and got the error like..

    Code:
    SQL0104N  An unexpected token "QUARTER" was found following "".  Expected 
    tokens may include:  ", )".  SQLSTATE=42601
    we are using DB2 ver 9 z/os..


    Code:
    SELECT t1.POLICY_LOC
         , t1.POLICY_NO
         , INT(COS(recv_qrtr - 1)) * cost_diff Q1
         , INT(COS(recv_qrtr - 2)) * cost_diff Q2
         , INT(COS(recv_qrtr - 3)) * cost_diff Q3
         , INT(COS(recv_qrtr - 4)) * cost_diff Q4
         , t2.RECV_DT
     FROM  TABLE1 T1
     INNER JOIN
           (SELECT t2.*
                 , recv_qrtr
                 , ROW_NUMBER()
                      OVER( PARTITION BY POLICY_LOC
                                       , POLICY_NO
                                       , recv_qrtr
                                ORDER BY RECV_DT DESC ) AS rn
             FROM  TABLE2 T2
             INNER JOIN LATERAL
                   (VALUES QUARTER(T2.RECV_DT) ) f(recv_qrtr)
             WHERE YEAR(RECV_DT) = '2012'
           ) t2
       ON 
     0=0
       AND rn = 1
     INNER JOIN LATERAL
           (VALUES T1.AM_COST - T2.AM_COST ) f(cost_diff)
    ON 
    0=0
    ;
    used INNER join instead of CROSS join...(system does not support CROSS join)

    Please help

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "using DB2 ver 9 z/os" is one of basic informations which was requested in http://www.dbforums.com/db2/854783-m...e-posting.html

    You should publish such information at the first to save your time and other persons' time,
    even if you published the informations in another thread.
    Everyone doesn't use same simgle system present and forever.

    By the way, try this...
    Code:
    SELECT t1.POLICY_LOC
         , t1.POLICY_NO
         , INT(COS(recv_qrtr - 1)) * cost_diff Q1
         , INT(COS(recv_qrtr - 2)) * cost_diff Q2
         , INT(COS(recv_qrtr - 3)) * cost_diff Q3
         , INT(COS(recv_qrtr - 4)) * cost_diff Q4
         , t2.RECV_DT
     FROM  TABLE1 T1
     INNER JOIN
           (SELECT t2.*
                 , recv_qrtr
                 , ROW_NUMBER()
                      OVER( PARTITION BY POLICY_LOC
                                       , POLICY_NO
                                       , recv_qrtr
                                ORDER BY RECV_DT DESC ) AS rn
             FROM  TABLE2 T2
             /*CROSS JOIN*/, /*LATERAL*/TABLE
                   (/*VALUES*/SELECT QUARTER(T2.RECV_DT)
                     FROM sysibm.sysdummy1) f(recv_qrtr)
             WHERE YEAR(RECV_DT) = '2012'
           ) t2
       ON  T2.POLICY_LOC = T1.POLICY_LOC
       AND T2.POLICY_NO  = T1.POLICY_NO
       AND rn = 1
     /*CROSS JOIN*/, /*LATERAL*/TABLE
           (/*VALUES*/SELECT T1.AM_COST - T2.AM_COST
             FROM sysibm.sysdummy1) f(cost_diff)
    ;
    Last edited by tonkuma; 01-13-12 at 06:53.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    VALUES clauses can be replaced by the following ways, too.
    Code:
    ...
    /*
                   (/*VALUES*/SELECT QUARTER(T2.RECV_DT)
                     FROM sysibm.sysdummy1) f(recv_qrtr)
    */
                   (/*VALUES*/SELECT QUARTER(T2.RECV_DT) AS recv_qrtr
                     FROM sysibm.sysdummy1) f/*(recv_qrtr)*/
    ...
    ...
    /*
           (/*VALUES*/SELECT T1.AM_COST - T2.AM_COST
             FROM sysibm.sysdummy1) f(cost_diff)
    */
           (/*VALUES*/SELECT T1.AM_COST - T2.AM_COST AS cost_diff
             FROM sysibm.sysdummy1) f/*(cost_diff)*/
    ;

Posting Permissions

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