OraFAQ Forum: SQL & PL/SQL » duplicate records with different date_from and date

Home » SQL & PL/SQL » SQL & PL/SQL » duplicate records with different date_from and date_to (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

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

duplicate records with different date_from and date_to [message #667693]

Wed, 10 January 2018 19:50

naga.oracle
Messages: 6
Registered: August 2014
Location: india

Junior Member

Hi Experts,

I have learned many things by vising this site and seeing the queries and answers provided by you.
here I am facing an issue, I tried my best to solve this, If you can help in this regard I would owe you a lot.

I have 3 tables and their data as fallows,

 create table cust_tab
 (
   cust_id Number(10),
   cust_name varchar2(30),
   first_name varchar2(30)
 );

 insert into cust_tab values(2400,'JOHN','MICHAEL');
 
 insert into cust_tab values(3215,'ALICE','MANDRID');
 
 insert into cust_tab values(5621,'MANUEL','SILVEIRA');


Create table Sub_Chain
 (
   Cust_id Number(10),
   Sub_Cust_Id Number(10),
   Date_From Date,
   Date_To Date
 );

 Insert into Sub_Chain Values(2400,8333,'01-JAN-2015','31-DEC-2015');
 
 Insert into Sub_Chain Values(2400,8333,'01-JAN-2018','31-DEC-2018');
 
 Insert into Sub_Chain Values(3215,6120,'01-JAN-2015','31-DEC-2015');
  
 Insert into Sub_Chain Values(3215,6120,'01-JAN-2019','31-DEC-2020');
 
 Insert into Sub_Chain Values(5621,1111,'01-JAN-2018','31-DEC-2018');


Create table Cust_Acc_Bal
 (
   Acc_Id Number(10),
   Cust_Id Number(10),
   Amount Number(10)
 );
 
 
 Insert Into Cust_Acc_Bal Values(10,2400,25000);
 
 Insert Into Cust_Acc_Bal Values(20,3215,50000);
 
 Insert Into Cust_Acc_Bal Values(30,5621,10000);

My query:

 Select Ct.Cust_Id,
        Sc.Sub_Cust_Id,
        Sc.Date_From,
        Sc.Date_To,
        Ab.Amount
 From cust_tab Ct,
      Sub_Chain Sc,
      Cust_Acc_Bal Ab
      Where Ct.Cust_Id = Sc.Cust_Id 
        And Sc.Cust_Id = Ab.Cust_Id;

There are duplicate records for cust_id and sub_cust_id with different date_from and date_to.

CUST_ID         SUB_CUST_ID            DATE_FROM        DATE_TO            AMOUNT
2400	            8333	       1/1/2015	       12/31/2015	   25000
2400	            8333	       1/1/2018	       12/31/2018	   25000
3215	            6120	       1/1/2015	       12/31/2015	   50000
3215	            6120	       1/1/2019	       12/31/2020	   50000
5621	            1111	       1/1/2018	       12/31/2018	   10000

In Sub_Chain table there are 2 records for Cust_Id and Sub_Cust_Id but with different dates(that means their working periods)
if we take Cust_Id 2400 and Sub_Cust_Id 8333 there are 2 records with different periods '01-JAN-2015' TO '31-DEC-2015' and '01-JAN-2018','31-DEC-2018'

here i want to restrict the data like if current system date is falling in one of the records then i should consider that record,

if the current system date is not falling any of the both records then i should consider any one of the record, that way i want to avoid the duplicates.
for the Cust_Id 3215 and Sub_Cust_Id 6120 there are 2 records with different periods '01-JAN-2015','31-DEC-2015' and '01-JAN-2019','31-DEC-2020'.

Expected output.

CUST_ID        SUB_CUST_ID          DATE_FROM           DATE_TO            AMOUNT
2400	         8333	            1/1/2018	       12/31/2018	   25000
3215	         6120	            1/1/2019	       12/31/2020	   50000
5621	         1111	            1/1/2018	       12/31/2018	   10000

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

could you please help me with this query.

Thanks in advance.

[EDITED by LF: applied [code] tags]

Attachment: Untitled.png
(Size: 13.51KB, Downloaded 543 times)

[Updated on: Thu, 11 January 2018 00:19] by Moderator

Report message to a moderator

Re: duplicate records with different date_from and date_to [message #667702 is a reply to message #667693]

Thu, 11 January 2018 02:20

quirks
Messages: 82
Registered: October 2014

Member

I think it might work as you expect, but there is probably a less ugly method:

WITH
    RANK_SUB_CHAIN AS
        (SELECT CUST_ID
               ,SUB_CUST_ID
               ,DATE_FROM
               ,DATE_TO
               ,CASE
                    WHEN SYSDATE BETWEEN DATE_FROM AND DATE_TO THEN 0
                    ELSE RANK() OVER(PARTITION BY CUST_ID, SUB_CUST_ID ORDER BY DATE_FROM DESC)
                END
                    AS RNK
           FROM SUB_CHAIN),
    FILT_SUB_CHAIN AS
        (SELECT CUST_ID
               ,SUB_CUST_ID
               ,DATE_FROM
               ,DATE_TO
               ,RNK
               ,CASE WHEN RNK = MIN(RNK) OVER(PARTITION BY CUST_ID, SUB_CUST_ID ORDER BY RNK ASC) THEN 'Y' ELSE 'N' END
                    AS KEEP_VALUES
           FROM RANK_SUB_CHAIN)
SELECT CT.CUST_ID, SC.SUB_CUST_ID, SC.DATE_FROM, SC.DATE_TO, AB.AMOUNT
  FROM CUST_TAB CT, FILT_SUB_CHAIN SC, CUST_ACC_BAL AB
 WHERE CT.CUST_ID = SC.CUST_ID AND SC.CUST_ID = AB.CUST_ID AND SC.KEEP_VALUES = 'Y';

[Updated on: Thu, 11 January 2018 02:21]

Report message to a moderator