OraFAQ Forum: SQL & PL/SQL » filtering in a table by ignoring the records those are unnecessary anymore

Home » SQL & PL/SQL » SQL & PL/SQL » filtering in a table by ignoring the records those are unnecessary anymore (oracle sql developer 4.1.3.20)

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

filtering in a table by ignoring the records those are unnecessary anymore [message #648317]

Sat, 20 February 2016 07:20

gujarati
Messages: 2
Registered: February 2016

Junior Member

Hello,

I have a table including CONTRACT_ID, ADDENDUM_ID and PAYMENT_MONTH (payment dates of a yearly contract).
Everytime an update occurs for any reason in the system:
1- ADDENDUM_ID udates as +1
2- PAYMENT_MONTH records are duplicated for the remaining months

In attached file I tried to explain in detail using an example (a contract with 3 updates).
The question is how to write a query to get a summary table ignoring the duplicated but unnecessary records because of a new update on addendum column.
Please note that there are thousands of contracts in the original table, while the example contains only one.

Thanks..
/forum/fa/13038/0/

[mod-edit: image inserted into message body by bb]

Attachment: example.png
(Size: 33.07KB, Downloaded 1244 times)

[Updated on: Sat, 20 February 2016 12:57] by Moderator

Report message to a moderator

Re: filtering in a table by ignoring the records those are unnecessary anymore [message #648318 is a reply to message #648317]

Sat, 20 February 2016 08:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: filtering in a table by ignoring the records those are unnecessary anymore [message #648320 is a reply to message #648318]

Sat, 20 February 2016 09:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Explain how, when & why records for payment_month 2, 3, 4, 5 for ADDENDUM_ID=1 get into the table?

Report message to a moderator

Re: filtering in a table by ignoring the records those are unnecessary anymore [message #648321 is a reply to message #648320]

Sat, 20 February 2016 09:51

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

select c_id ,max(ad_id),p_m from org_t group by c_id,p_m

Report message to a moderator

Re: filtering in a table by ignoring the records those are unnecessary anymore [message #648323 is a reply to message #648320]

Sat, 20 February 2016 10:35

gujarati
Messages: 2
Registered: February 2016

Junior Member

When there is an update in the addendum_id, the rule in the system automatically duplicates the records of remaining months.

In this example,
The first 12 line belongs to addendum_id 1, these records created at the beginning of the contract and this is the first update.
Then on 5th month there was 2nd update and the remaining months' records (5 to 12) were duplicated in the table.
Then on 10th month there was 3rd update and the remaining months' records (10 to 12) were duplicated again in the table.

When I filter as live4learn suggested it gives only the records after latest update (addendum_id 3); in this case 10 to 12.
This is OK however I also need to get the months 1 to 4 from addendum_id 1 and months 5-9 from addendum_id 2.

I hope it is clear now.

Report message to a moderator

Re: filtering in a table by ignoring the records those are unnecessary anymore [message #648324 is a reply to message #648323]

Sat, 20 February 2016 11:13

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

create table org_t (c_id number,ad_id number,p_m number)

insert all
into org_t values(155,1,1)
into org_t values(155,1,2)
into org_t values(155,1,3)
into org_t values(155,1,4)
into org_t values(155,1,5)
into org_t values(155,1,6)
into org_t values(155,1,7)
into org_t values(155,1, Cool

into org_t values(155,1,9)
into org_t values(155,1,10)
into org_t values(155,1,11)
into org_t values(155,1,12)
into org_t values(155,2,5)
into org_t values(155,2,6)
into org_t values(155,2,7)
into org_t values(155,2, Cool

into org_t values(155,2,9)
into org_t values(155,2,10)
into org_t values(155,2,11)
into org_t values(155,2,12)
into org_t values(155,3,10)
into org_t values(155,3,11)
into org_t values(155,3,12)
select * from dual

insert into org_t(c_id,ad_id,p_m)
select c_id+1,ad_id,p_m from org_t
commit;

SQL> select * from (
2 select c_id ,max(ad_id),p_m from org_t group by c_id,p_m
3 ) order by 1
4 ;

C_ID MAX(AD_ID) P_M
---------- ---------- ----------
155 1 1
155 1 2
155 1 3
155 1 4
155 2 5
155 2 6
155 2 7
155 2 8
155 2 9
155 3 10
155 3 11
155 3 12
156 1 1
156 1 2
156 1 3
156 1 4
156 2 5
156 2 6
156 2 7
156 2 8

C_ID MAX(AD_ID) P_M
---------- ---------- ----------
156 2 9
156 3 10
156 3 11
156 3 12

24 rows selected

SQL>

Report message to a moderator

Re: filtering in a table by ignoring the records those are unnecessary anymore [message #648325 is a reply to message #648324]

Sat, 20 February 2016 13:27

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

Senior Member
Account Moderator

@live4learn,

Please read and apply How to use [code] tags and make your code easier to read.

Report message to a moderator

Previous Topic:	Query
Next Topic:	data view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 06:05:24 CDT 2024