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 #632460] |
Sun, 01 February 2015 04:52 |
|
shumail
Messages: 149 Registered: September 2012 Location: Canada
|
Senior Member |
|
|
Hello
I have got stuck on a query and appreciate if someone help me out...
I have got the following sample data:
Sample Data
WITH datum AS
(SELECT 100 grp,
21 TB ,
to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-MAY-2013','dd-mon-yyyy') cov_ter_dt,
5 AMT
FROM dual
UNION ALL
SELECT 100 grp,
22 TB ,
to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
10
FROM dual
UNION ALL
SELECT 100 grp,
23 TB ,
to_date('10-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
15
FROM dual
UNION ALL
SELECT 100 grp,
21 TB ,
to_date('01-JUN-2013','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
20
FROM dual
)
SELECT * FROM datum;
Query Result
GRP, TB, COV_EFF_DT, COV_TER_DT, AMT
100 21 01-DEC-10 31-MAY-13 5
100 22 01-DEC-10 31-DEC-44 10
100 23 10-DEC-10 31-DEC-44 15
100 21 01-JUN-13 31-DEC-44 20
Required Output
GRP Effective Date AMT
100 10-DEC-10 30 -- 5+10+15)
100 01-JUN-10 45 -- (20+10+15) =45 --current row cov_eff_dt =01-JUN-13 and row 2 and 3 cov_ter_dt is greater than current cov_eff_dt so take current row amt and 2nd and 3rd row amt....
Rules
-- System will create record whenever COV_EFF_DT change, in my above example COV_EFF_DT changed 2 times so 2 records needs to be created.
-- Calculation of AMT is little bit tricky, When the Cov_eff_dt changes then system get the current AMT and get the AMT of all previous AMT where all previous COV_TER_DT > current COV_EFF_DT....
Thanks in advance
Regards
Shu
|
|
|
|
Re: How to make logic [message #632473 is a reply to message #632460] |
Sun, 01 February 2015 07:50 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Your reqs are not clear. Something like:
WITH datum AS
(SELECT 100 grp,
21 TB ,
to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-MAY-2013','dd-mon-yyyy') cov_ter_dt,
5 AMT
FROM dual
UNION ALL
SELECT 100 grp,
22 TB ,
to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
10
FROM dual
UNION ALL
SELECT 100 grp,
23 TB ,
to_date('10-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
15
FROM dual
UNION ALL
SELECT 100 grp,
21 TB ,
to_date('01-JUN-2013','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
20
FROM dual
),
t as (
select grp,
row_number() over(partition by grp order by cov_eff_dt,tb) rn,
case lead(tb) over(partition by grp order by cov_eff_dt)
when tb + 1 then 0
else 1
end end_of_group,
cov_eff_dt,
cov_ter_dt,
amt
from datum
)
select grp,
cov_eff_dt,
(
select sum(amt) + t1.amt
from t t2
where t2.grp = t1.grp
and t2.rn < t1.rn
and t2.cov_ter_dt > t1.cov_eff_dt
) amt
from t t1
where end_of_group = 1
order by grp,
cov_eff_dt
/
GRP COV_EFF_D AMT
--------- --------- ----------
100 10-DEC-10 30
100 01-JUN-13 45
QL>
SY.
[Updated on: Sun, 01 February 2015 07:51] Report message to a moderator
|
|
|
Re: How to make logic [message #632501 is a reply to message #632460] |
Mon, 02 February 2015 01:09 |
|
shumail
Messages: 149 Registered: September 2012 Location: Canada
|
Senior Member |
|
|
Thank you @Michel and Solomon for you replies.
@Michel you are rite, I did something wrong in my posting, let me do explain it again...
I have the following sample data
Sample data
Query Result
GRP, TB, COV_EFF_DT, COV_TER_DT, AMT
100 21 01-DEC-10 31-MAY-13 5
100 22 01-DEC-10 31-DEC-44 10
100 23 10-DEC-10 31-DEC-44 15
100 21 01-JUN-13 31-DEC-44 20
In above case we need to create 3 records because we have 3 different COV_EFF_DT dates.
First record for row no 1 and 2 , we have similar COV_EFF_DT that's why same COV_EFF_DT roll up into one.
Second record for row no 3
Third record for row no 4
How to create AMT for first record by using row #1 and 2
Rollup 1st and 2nd rows because COV_EFF_DT for row 1 and 2 are same and then sum AMT, for example:
When current row is 1st row then amt is 5.
when current row is 2 then AMT should be current AMT plus all previous AMT where all previous COV_TER_DT > current row COV_EFF_DT.
How to create AMT for Second record by using row #3, we cannot include 4th row because COV_EFF_DT is different
current row COV_EFF_DT and AMT should be current AMT plus all previous AMT where all previous COV_TER_DT > current row COV_EFF_DT... for example..
3rd row AMT is 15 , and 1st and 2nd row COV_TER_DT >current row COV_EFF_DT so 15+10+5=30
How to create AMT for Third record by using row #4
same logic like second record,should be current row AMT + row #3 and 4 AMT, we don't consider AMT for 1 row because 1st row COV_TER_DT <= current row (4) COV_EFF_DT..
Output should be like below:
GRP, COV_EFF_DT, COV_TER_DT, AMT
100 01-DEC-10 31-DEC-44 15 (10+5)
100 10-DEC-10 31-DEC-44 30 (15+10+5)
100 01-JUN-13 31-DEC-44 45 (20+15+10)
My English is not good , please let me know if you have any questions about it... Thanks
Regards
Shu
[Lalit : Removed superfluous lines in the end]
[Updated on: Mon, 02 February 2015 01:11] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: How to make logic [message #632614 is a reply to message #632610] |
Tue, 03 February 2015 10:50 |
|
shumail
Messages: 149 Registered: September 2012 Location: Canada
|
Senior Member |
|
|
Please see blow my updated code:
Code
WITH datum AS
(SELECT 100 grp,
21 TB ,
to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-MAY-2013','dd-mon-yyyy') cov_ter_dt,
5 AMT
FROM dual
UNION ALL
SELECT 100 grp,
22 TB ,
to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
10
FROM dual
UNION ALL
SELECT 100 grp,
23 TB ,
to_date('10-DEC-2010','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
15
FROM dual
UNION ALL
SELECT 100 grp,
21 TB ,
to_date('01-JUN-2013','dd-mon-yyyy') cov_eff_dt ,
to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,
20
FROM dual
),
t as (
select grp,
row_number() over(partition by grp order by cov_eff_dt) rn,
case lead( cov_eff_dt) over(partition by grp order by cov_eff_dt)
when cov_eff_dt + 1 then 0
else 1
end end_of_group,
cov_eff_dt,
cov_ter_dt,
amt
from datum
)
select grp,
cov_eff_dt,rn,end_of_group,
(
select sum(amt) + t1.amt
from t t2
where t2.grp = t1.grp
and t2.rn < t1.rn
and t2.cov_ter_dt > t1.cov_eff_dt
) amt
from t t1
where end_of_group = 1
order by grp,
cov_eff_dt;
|
|
|
|
|
Re: How to make logic [message #632724 is a reply to message #632674] |
Wed, 04 February 2015 23:04 |
|
shumail
Messages: 149 Registered: September 2012 Location: Canada
|
Senior Member |
|
|
My new logic is below:
with datum as
(
Select 100 grp, 21 TB ,to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt , to_date('31-MAY-2013','dd-mon-yyyy') cov_ter_dt,5000 amt from dual
union all
Select 100 grp, 22 TB ,to_date('01-DEC-2010','dd-mon-yyyy') cov_eff_dt , to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt, 2500 from dual
union all
Select 100 grp, 23 TB ,to_date('10-DEC-2010','dd-mon-yyyy') cov_eff_dt , to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,2500 from dual
union all
Select 100 grp, 21 TB ,to_date('01-JUN-2013','dd-mon-yyyy') cov_eff_dt , to_date('31-DEC-4444','dd-mon-yyyy') cov_ter_dt,5000 from dual
)
select x.grp, x.tb, x.cov_eff_dt,min(d.cov_ter_dt) as cov_ter_dt, sum(d.amt) as total
from
(
select *
from(
Select d.*, case when cov_eff_dt!=lag(cov_eff_dt,1,cov_eff_dt)over(partition by grp order by cov_eff_dt) then 1 END as flg
from datum d
) where flg=1
)x join datum d
on x.grp=d.grp and x.cov_eff_dt < d.cov_ter_dt and d.cov_eff_dt<=x.cov_eff_dt
group by x.grp, x.tb, x.cov_eff_dt
;
|
|
|
Goto Forum:
Current Time: Wed Apr 17 14:39:22 CDT 2024
|