Home » SQL & PL/SQL » SQL & PL/SQL » How to make logic (Oracle, Oracle Database 11g Express Edition Release 11.2.0.2.0 ,Win 7 Pro)
| How to make logic [message #620533] |
Mon, 04 August 2014 02:09  |
 |
shumail
Messages: 149
Registered: September 2012
Location: Canada
|
Senior Member |
|
|
Hi All
Sometime it looks impossible to follow some logic and create SQL or PLSQL, this thing happened with me and it looks impossible for me to implement this logic. I really appreciate if someone helps me out.
I have the following sample data:
Sample Data
WITH DATUM AS
(
SELECT '71121' grp,'001' SCT,'Life' plans,to_date('01-AUG-2000','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-12-2009','DD-MM-YYYY') TERMINATION_DATE ,to_number(5000) cov_amt FROM DUAL
UNION ALL
SELECT '71121' grp,'001' SCT,'OP_Life' plans,to_date('01-FEB-2006','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-07-2013','DD-MM-YYYY') TERMINATION_DATE ,to_number(2000) cov_amt FROM DUAL
UNION ALL
SELECT '71121' grp,'001' SCT,'OP_SPU' plans,to_date('01-FEB-2006','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-07-2013','DD-MM-YYYY') TERMINATION_DATE ,to_number(2000) cov_amt FROM DUAL
UNION ALL
SELECT '71121' grp,'001' SCT,'LIFE' plans,to_date('01-JAN-2010','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-12-4444','DD-MM-YYYY') TERMINATION_DATE ,to_number(8000) cov_amt FROM DUAL
UNION ALL
SELECT '71121' grp,'001' SCT,'OP_Life' plans,to_date('01-AUG-2013','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-12-4444','DD-MM-YYYY') TERMINATION_DATE ,to_number(3000) cov_amt FROM DUAL
UNION ALL
SELECT '71121' grp,'001' SCT,'OP_SPU' plans,to_date('01-AUG-2013','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-12-4444','DD-MM-YYYY') TERMINATION_DATE ,to_number(3000) cov_amt FROM DUAL
ORDER BY GRP,SCT,EFFECTIVE_DATE,plans
)
select * from datum
;
Query Result
grp SCT plans EFFECTIVE_DATE TERMINATION_DATE cov_amt
71121 001 Life 01-AUG-00 31-DEC-09 5000
71121 001 OP_Life 01-FEB-06 31-JUL-13 2000
71121 001 OP_SPU 01-FEB-06 31-JUL-13 2000
71121 001 LIFE 01-JAN-10 31-DEC-44 8000
71121 001 OP_Life 01-AUG-13 31-DEC-44 3000
71121 001 OP_SPU 01-AUG-13 31-DEC-44 3000
Rules
--If all plans =LIFE against grp and sct then no need to create records
--We always need to create records when effective date change except 1st record thats should always plan = life Means skip 1st records always.
-- If you see sample data then there are 4 different effective dates but as I mentioned that
--we dont need to create record of plan = Life because its a first record so total records should be 3 .
--last record termination date always be NULL
--If we follow the above rule then first records should be like below:
grp sct plans EFFECTIVE_DATE TERMINATION_DATE cov_amt
71121 001 OP_SPU 01-FEB-06 31-DEC-09( 01-JAN-10-1) 9000+2000+2000+5000)
--Logic for Cov_amt
-- we sum up cov_amt including life cov_amt to the previous records if previous all records effective date is less than or equal to the current record effective date
-- for 2nd time plan=Life we dont include cov_amt for 1st Life and then follow the above rule
--Logic for Termination_date
--termination date always less than -1 from next effective_date
---------
--By using the above rules I need the following output
Output
grp sct plans EFFECTIVE_DATE TERMINATION_DATE cov_amt
71121 001 NULL 01-FEB-06 31-DEC-09/*( 01-JAN-10-1)* 9000--(2000+2000+5000)--include 1st time plan=Life cov_amt
71121 001 NULL 01-JAN-10 31-JUL-2013/* (01-AUG-13-1)*/ 12000 --(8000+2000+2000) --not include 1st time plan=Life cov_amt
71121 001 NULL 01-AUG-13 NULL/* (01-AUG-13-1)*/ 14000 --(3000+3000+8000) --include 2nd time plan=Life cov_amt
------------------
I try to convey my message at my level best but I know the logic is very confusing so if you want more info then please do let me know. Thanks in advance
Regards
Shumail
|
|
|
|
|
|
|
|
|
|
| Re: How to make logic [message #620537 is a reply to message #620533] |
Mon, 04 August 2014 02:42  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Shumail,
Do you mean that the first row could be considered for whatever the following order returns?
ORDER BY GRP,SCT,EFFECTIVE_DATE,plans
|
|
|
|
|
|
|
|
| Re: How to make logic [message #620541 is a reply to message #620539] |
Mon, 04 August 2014 02:48 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
shumail wrote on Mon, 04 August 2014 08:44There are 6 rows in sample data provided and I mean to say 1st row of sample data.
Rows in a table (and rows in a with clause) have no inherent order. If you want the data in a particular order then it needs to be ordered by one or more columns.
If you don't have columns that can be used to order the data then you'll need to add some.
So what columns should the data be ordered by?
|
|
|
|
|
|
| Re: How to make logic [message #620544 is a reply to message #620533] |
Mon, 04 August 2014 03:43 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
shumail wrote on Mon, 04 August 2014 12:39
SELECT '71121' grp,'001' SCT,'LIFE' plans,to_date('01-JAN-2010','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-12-4444','DD-MM-YYYY') TERMINATION_DATE ,to_number(8000) cov_amt FROM DUAL
Year 4444 belongs to which century?
SQL> WITH datum AS
2 (SELECT '71121' grp,
3 '001' sct,
4 'Life' plans,
5 to_date('01-AUG-2000', 'DD-MM-YYYY') effective_date,
6 to_date('31-12-2009', 'DD-MM-YYYY') termination_date,
7 to_number(5000) cov_amt
8 FROM dual
9 UNION ALL
10 SELECT '71121' grp,
11 '001' sct,
12 'OP_Life' plans,
13 to_date('01-FEB-2006', 'DD-MM-YYYY') effective_date,
14 to_date('31-07-2013', 'DD-MM-YYYY') termination_date,
15 to_number(2000) cov_amt
16 FROM dual
17 UNION ALL
18 SELECT '71121' grp,
19 '001' sct,
20 'OP_SPU' plans,
21 to_date('01-FEB-2006', 'DD-MM-YYYY') effective_date,
22 to_date('31-07-2013', 'DD-MM-YYYY') termination_date,
23 to_number(2000) cov_amt
24 FROM dual
25 UNION ALL
26 SELECT '71121' grp,
27 '001' sct,
28 'LIFE' plans,
29 to_date('01-JAN-2010', 'DD-MM-YYYY') effective_date,
30 to_date('31-12-4444', 'DD-MM-YYYY') termination_date,
31 to_number(8000) cov_amt
32 FROM dual
33 UNION ALL
34 SELECT '71121' grp,
35 '001' sct,
36 'OP_Life' plans,
37 to_date('01-AUG-2013', 'DD-MM-YYYY') effective_date,
38 to_date('31-12-4444', 'DD-MM-YYYY') termination_date,
39 to_number(3000) cov_amt
40 FROM dual
41 UNION ALL
42 SELECT '71121' grp,
43 '001' sct,
44 'OP_SPU' plans,
45 to_date('01-AUG-2013', 'DD-MM-YYYY') effective_date,
46 to_date('31-12-4444', 'DD-MM-YYYY') termination_date,
47 to_number(3000) cov_amt
48 FROM dual)
49 SELECT grp,
50 sct,
51 plans,
52 effective_date,
53 lead(effective_date) over(ORDER BY grp, sct, effective_date, plans) - 1 term_dt,
54 termination_date,
55 cov_amt
56 FROM datum
57 ORDER BY grp, sct, effective_date, plans
58 /
GRP SCT PLANS EFFECTIVE_DATE TERM_DT TERMINATION_DATE COV_AMT
----- --- ------- -------------- ----------- ---------------- ----------
71121 001 Life 08/01/2000 01/31/2006 12/31/2009 5000
71121 001 OP_Life 02/01/2006 01/31/2006 07/31/2013 2000
71121 001 OP_SPU 02/01/2006 12/31/2009 07/31/2013 2000
71121 001 LIFE 01/01/2010 07/31/2013 12/31/4444 8000
71121 001 OP_Life 08/01/2013 07/31/2013 12/31/4444 3000
71121 001 OP_SPU 08/01/2013 12/31/4444 3000
6 rows selected
While using LEAD(effective_date) to get the previous day of next effective date, i.e. TERM_DT in my query, how to relate it with TERMINATION_DATE?. So please clarify what sort of daate is it?
[Updated on: Mon, 04 August 2014 03:51] Report message to a moderator |
|
|
|
| Re: How to make logic [message #620545 is a reply to message #620533] |
Mon, 04 August 2014 03:54 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
shumail wrote on Mon, 04 August 2014 12:39
SELECT '71121' grp,'001' SCT,'Life' plans,to_date('01-AUG-2000','DD-MM-YYYY')EFFECTIVE_DATE,to_date('31-12-2009','DD-MM-YYYY') TERMINATION_DATE ,to_number(5000) cov_amt FROM DUAL
And what is the to_number doing there? You built your own table using with clause. Is cov_amt not a NUMBER type in the actual table? 5000 is a number, isn't it?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to make logic [message #620631 is a reply to message #620629] |
Mon, 04 August 2014 11:50 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
shumail wrote on Mon, 04 August 2014 22:14Really appreciate if someone help me out. Thanks
You have not answered to my posts above. I have posted a test case with outstanding questions, we can proceed only if you answer to those outstanding questions.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:28:13 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
