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

    Unanswered: subtraction in select

    The below select query wiil have to do the calculation

    1,If the month(RECV_DT) is in Jan-Mar -->AM_COST value subtract from Q1 column value and
    has to display in the Q1 coulmn
    2.Apr-jun-->above same functionality will perform in the Q2 column
    3.jul-sep-->abovesame functionality will perform in the Q3 column
    4.oct-dec-->abovesame functionality will perform in the Q4 column


    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
    Code:
    select 
       t1.policy_no
    ,t1.Q1
    ,T1.Q2
    ,T1.Q3
    ,T1.Q4
    ,T2.AM_COST
    ,T2.RECV_DT
    
    FROM 
      TABLE1 T1
    INNER JOIN
     TABLE2 T1
    ON
    
    T1.POLICY_NO=T2.POLICY_NO
    Code:
    table1
    
    POLICY_NO      Q1       Q2      Q3      Q4           
    
    1234            250     0       0       50    
    1234            0       75      0       50    
    1234            0       0       500     50    
    1234            0       0       0       25    
       
    4444            80      145     0       20     
    4444            0       145     100     20     
      
    AB              0       145     0       20    
    AB              0       145     0       20     
    AB              0       145     0       200
    Code:
    table2
    
    POLICY_NO          AM_COST RECV_DT        
    
    1234                100    2011-03-19  
    1234                100    2011-04-19 
    1234                100    2011-09-19 
    1234                100    2011-12-22
       
    4444                60    2011-01-30     
    4444                60    2011-07-30 
      
    AB                  60    2011-10-31
    AB                  60    2011-11-30
    AB                  60    2011-12-30-->pick the LATEST DATE
    In the table2

    AB 60 2011-10-31
    AB 60 2011-11-30
    AB 60 2011-12-30-->LATEST DATE


    the latest date (2011-12-30) row should fetch and do the calculation
    EXPECTED RESULT
    Code:
    POLICY_NO      Q1       Q2      Q3      Q4    AM_COST RECV_DT        
    
    1234            150      0       0       50    100    2011-03-19  
    1234            0       25       0       50    100    2011-04-19 
    1234            0        0       400     50    100    2011-09-19 
    1234            0        0       0       75    100    2011-12-22   
    4444            20      145      0       20     60    2011-01-30    
    4444            0       145      40      20     60    2011-07-30      
    AB              0       145      0       140    60    2011-12-30
    please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Before considering calculations, you should review your join condition.
    I already pointed out the following issue by Example 2 in this thread http://www.dbforums.com/db2/1672058-...-date-row.html

    You should learn by your experience.

    The join condition was "T1.POLICY_NO=T2.POLICY_NO"
    and the values of both tables include duplicated values for joined columns.

    The result of join would include
    (Number of duplicated POLICY_NO in T1) * (Number of duplicated POLICY_NO in T2)
    rows for each value of POLICY_NO.

    For example:
    Number of rows in the result of join from your sample data would be
    Code:
    POLICY_NO = 1234: 4 * 4 = 16 rows
    POLICY_NO = 4444: 2 * 2 =  4 rows
    POLICY_NO = AB  : 3 * 3 =  9 rows

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you executed the query by yourself?
    It returned the error
    SQL0203N A reference to column "T1.POLICY_NO" is ambiguous. SQLSTATE=42702

    Here is the result of corrected query.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table1(policy_no , q1 , q2 , q3 , q4) AS (
    VALUES
      ( '1234' , 250 ,   0 ,   0 ,  50 )
    , ( '1234' ,   0 ,  75 ,   0 ,  50 )
    , ( '1234' ,   0 ,   0 , 500 ,  50 )
    , ( '1234' ,   0 ,   0 ,   0 ,  25 )
    
    , ( '4444' ,  80 , 145 ,   0 ,  20 )
    , ( '4444' ,   0 , 145 , 100 ,  20 )
    
    , ( 'AB'   ,   0 , 145 ,   0 ,  20 )
    , ( 'AB'   ,   0 , 145 ,   0 ,  20 )
    , ( 'AB'   ,   0 , 145 ,   0 , 200 )
    )
    , table2(policy_no , am_cost , recv_dt) AS (
    VALUES
      ( '1234' , 100 , '2011-03-19' )
    , ( '1234' , 100 , '2011-04-19' )
    , ( '1234' , 100 , '2011-09-19' )
    , ( '1234' , 100 , '2011-12-22' )
    
    , ( '4444' ,  60 , '2011-01-30' )
    , ( '4444' ,  60 , '2011-07-30' )
    
    , ( 'AB'   ,  60 , '2011-10-31' )
    , ( 'AB'   ,  60 , '2011-11-30' )
    , ( 'AB'   ,  60 , '2011-12-30' ) --> pick the LATEST DATE
    )
    select 
       t1.policy_no
    ,t1.Q1
    ,T1.Q2
    ,T1.Q3
    ,T1.Q4
    ,T2.AM_COST
    ,T2.RECV_DT
    
    FROM 
      TABLE1 T1
    INNER JOIN
     TABLE2 /*T1*/ T2
    ON
    
    T1.POLICY_NO=T2.POLICY_NO
    ;
    ------------------------------------------------------------------------------
    
    POLICY_NO Q1          Q2          Q3          Q4          AM_COST     RECV_DT   
    --------- ----------- ----------- ----------- ----------- ----------- ----------
    1234              250           0           0          50         100 2011-03-19
    1234              250           0           0          50         100 2011-12-22
    1234              250           0           0          50         100 2011-09-19
    1234              250           0           0          50         100 2011-04-19
    1234                0          75           0          50         100 2011-03-19
    1234                0          75           0          50         100 2011-12-22
    1234                0          75           0          50         100 2011-09-19
    1234                0          75           0          50         100 2011-04-19
    1234                0           0         500          50         100 2011-03-19
    1234                0           0         500          50         100 2011-12-22
    1234                0           0         500          50         100 2011-09-19
    1234                0           0         500          50         100 2011-04-19
    1234                0           0           0          25         100 2011-03-19
    1234                0           0           0          25         100 2011-12-22
    1234                0           0           0          25         100 2011-09-19
    1234                0           0           0          25         100 2011-04-19
    4444               80         145           0          20          60 2011-01-30
    4444               80         145           0          20          60 2011-07-30
    4444                0         145         100          20          60 2011-01-30
    4444                0         145         100          20          60 2011-07-30
    AB                  0         145           0          20          60 2011-10-31
    AB                  0         145           0          20          60 2011-12-30
    AB                  0         145           0          20          60 2011-11-30
    AB                  0         145           0          20          60 2011-10-31
    AB                  0         145           0          20          60 2011-12-30
    AB                  0         145           0          20          60 2011-11-30
    AB                  0         145           0         200          60 2011-10-31
    AB                  0         145           0         200          60 2011-12-30
    AB                  0         145           0         200          60 2011-11-30
    
      29 record(s) selected.

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

    Apology for the confusion..

    we have another one column in join condition..that is DISTINCT column

    Table1 and Table2 have

    Policy_det
    AA
    BB
    CC
    DD
    AA
    BB
    AA
    BB
    CC

    JOIN CONTION SHOULD BE

    ON
    T1.POLICY_NO=T2.POLICY_NO
    T1.POLICY_DET-T2.POLICY_DET

    thanks

Posting Permissions

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