| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-28-11, 10:00
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
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
|
|
11-28-11, 17:37
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Before considering calculations, you should review your join condition.
I already pointed out the following issue by Example 2 in this thread query returns latest date row
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
|
|
11-28-11, 19:48
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
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.
|
|
11-29-11, 01:42
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
subtraction in select
