| |
|
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.
|
 |
01-07-12, 08:32
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
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
|
|
01-07-12, 09:47
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
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 10:01.
Reason: Add Note.
|
01-08-12, 22:12
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 124
|
|
|
|
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;
|
|
01-08-12, 23:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
01-10-12, 20:46
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
Originally Posted by tonkuma
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)
;
|
|
01-13-12, 05:16
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
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
|
|
01-13-12, 05:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
"using DB2 ver 9 z/os" is one of basic informations which was requested in Must Read before posting
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 05:53.
|
01-13-12, 06:05
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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)*/
;
|
|
| 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
>
select query should fetch latest date calculation
