Good afternoon,
Your help with the following would be much appreciated (Sample data below)
I have highlighted what I want to flag as returned as duplicates
Definition of duplicate:
account_num the same
tran_effective_date maximum of 20 days apart
tran_processed_date maximum 10 days apart
tran_amount the same
However, I don't want to return a duplicate set if they don't both have a tran_priced_date populated.
So given the above
N100283 would not qualify even though the tran_effective_date and tran_processed_date are within 20 and 20 days respectively, one has a tran_priced date populated but the other doesn't
N101640 & N102395 doesn't qualify because both don't have the trab_priced_date populated
N108876 doesn't qualify as duplicate as the tran_effective_dates are more than 20 days apart.
Your help would be much appreciated.
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE samp_data';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
DBMS\_OUTPUT.put\_line('');
ELSE
RAISE;
END IF;
END;
/
CREATE TABLE samp_data (
ACCOUNT_NUM VARCHAR2(17),
TRAN_ID NUMBER(10),
TRAN_TYPE VARCHAR2(50),
TRAN_EFFECTIVE_DATE TIMESTAMP(6),
TRAN_PROCESSED_DATE TIMESTAMP(6),
TRAN_PRICED_DATE TIMESTAMP(6),
TRAN_AMOUNT NUMBER(13,2)
);
/
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N100283',140119178,'Regular With',to_timestamp('01-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('22-MAY-15 07.00.34.235000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('21-MAY-15 03.26.18.954000000 AM','DD-MON-RR HH.MI.SS.FF AM'),200);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N100283',140158525,'Regular With',to_timestamp('13-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('26-MAY-15 08.39.14.090000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,200);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N100284',140118826,'Regular With',to_timestamp('03-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('22-MAY-15 07.00.19.072000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('20-MAY-15 03.25.05.438000000 AM','DD-MON-RR HH.MI.SS.FF AM'),450);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N100284',140158120,'Regular With',to_timestamp('06-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('26-MAY-15 08.38.42.064000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('21-MAY-15 03.26.18.954000000 AM','DD-MON-RR HH.MI.SS.FF AM'),450);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N101640',140118957,'Regular With',to_timestamp('18-MAY-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('22-MAY-15 07.00.25.015000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,120);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N101640',140158278,'Regular With',to_timestamp('22-MAY-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('26-MAY-15 08.38.56.228000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,130);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N102395',140118842,'Regular With',to_timestamp('03-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('22-MAY-15 07.00.19.665000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,250);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N102395',140158235,'Regular With',to_timestamp('03-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('26-MAY-15 08.38.53.093000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,250);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N102396',140118823,'Regular With',to_timestamp('09-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('22-MAY-15 07.00.18.931000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('19-MAY-15 03.26.18.954000000 AM','DD-MON-RR HH.MI.SS.FF AM'),750);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N102396',140158099,'Regular With',to_timestamp('16-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('26-MAY-15 08.38.39.443000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('21-MAY-15 03.26.18.954000000 AM','DD-MON-RR HH.MI.SS.FF AM'),750);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N102827',140118850,'Regular With',to_timestamp('03-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('22-MAY-15 07.00.20.045000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,157.84);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N102827',140158118,'Regular With',to_timestamp('03-JUN-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('26-MAY-15 08.38.41.861000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,157.84);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N108876',139840720,'Regular With',to_timestamp('01-MAY-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('11-MAY-15 08.35.34.646000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('20-MAY-15 03.25.05.438000000 AM','DD-MON-RR HH.MI.SS.FF AM'),1000);
Insert into samp_data (ACCOUNT_NUM,TRAN_ID,TRAN_TYPE,TRAN_EFFECTIVE_DATE,TRAN_PROCESSED_DATE,TRAN_PRICED_DATE,TRAN_AMOUNT) values ('N108876',139889880,'Regular With',to_timestamp('22-MAY-15 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('12-MAY-15 08.49.29.080000000 AM','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('21-MAY-15 03.26.18.954000000 AM','DD-MON-RR HH.MI.SS.FF AM'),1000);
/
select * from samp_data
ORDER BY account_num, tran_effective_date, tran_processed_date;