OraFAQ Forum: SQL & PL/SQL » How to make logic

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: How to make logic [message #632465 is a reply to message #632460]

Sun, 01 February 2015 06:30

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

in my above example COV_EFF_DT changed 2 times

No, it changes 3 times: 21->22, 22->23, 23->21. Or explain what "change" means.
Explain each line and each column of the result and the reason is the following is not understandable:

Quote:

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....

Report message to a moderator

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 #632521 is a reply to message #632501]

Mon, 02 February 2015 06:32

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

This is no different from solution I posted. Just change LEAD from tb to cov_eff_dt and adjust CASE logic.

SY.

Report message to a moderator

Re: How to make logic [message #632530 is a reply to message #632521]

Mon, 02 February 2015 10:02

shumail
Messages: 149
Registered: September 2012
Location: Canada

Senior Member

Hi Solomon
Appreciate your reply, I updated code as per your suggestion but now I 'm getting one extra row.. see below result after code updation.

[b]GRP,    COV_EFF_DT,     AMT[/b]
100	10-12-01	
100	10-12-01	15
100	10-12-10	30
100	13-06-01	45

1st record is invalid, in addition, I really appreciate if you can explain your provided query for my learning purpose. Thanks in advance.

Regards
Shu

Report message to a moderator

Re: How to make logic [message #632610 is a reply to message #632530]

Tue, 03 February 2015 09:56

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

Please put your updated code here. Without that how one can check if the modified code is ok?

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;

Report message to a moderator

Re: How to make logic [message #632617 is a reply to message #632614]

Tue, 03 February 2015 11:24

shumail
Messages: 149
Registered: September 2012
Location: Canada

Senior Member

Hi All

I use some different logic and got my required output and now problem is solved. I really thankfully to all of your help.

Regards
Shu

Report message to a moderator

Re: How to make logic [message #632674 is a reply to message #632617]

Wed, 04 February 2015 03:14

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

Quote:

I use some different logic and got my required output and now problem is solved

then you must put your different logic here.

Report message to a moderator

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
;

Report message to a moderator

Previous Topic:	What is the effect of index for DELETE operation
Next Topic:	Explain code

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:31:22 CDT 2024