Link to home
Start Free TrialLog in
Avatar of B_rider
B_rider

asked on

Coding an Dynamic Date Interval that identify duplicates in rows

I have a Table with Three Columns:

SUBSCRIPTION_ID      SERVREQ_HEADER_ID       ACTIVITY_DT
23399505      292601967            27/07/2016
2808783            286971613            28/05/2016
555283            280376335            06/03/2016
23399505      238892180            30/09/2014
555283            280376158            06/03/2016
2808783            263311971            12/08/2015
555283            280375788            06/03/2016
320899            337109024            18/08/2017
320899            337741717            23/08/2017
320899            337461638            21/08/2017
320899            338246919            27/08/2017
2808783            365329629            13/04/2018
2808783            365329641            13/04/2018

This is an event table with SERVREQ_HEADER_ID being the unique indicator

1) I need dynamic code that looks at an SERVREQ_HEADER_ID  event for a particular subscription_id then looks 30 days back  and any records prior to that event adds a duplicate indicator field.

2) Also retrieve the earliest date in that 30 day period and updates the SERVREQ_HEADER_ID  event with a the earliest date in the 30 day interval

Would appreciate any Oracle SQL or PLSQL code help for this.

Many Thanks!!
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Can you post the expected results from that sample data?
Avatar of B_rider

ASKER

SUBSCRIPTION_ID       SERVREQ_HEADER_ID      ACTIVITY_DT      DUP_RECORD
320899      337109024      18/08/2017      Y
320899      337461638      21/08/2017      Y
320899      337741717      23/08/2017      Y
320899      338246919      18/08/2017      N
555283      280375788      06/03/2016      Y
555283      280376158      06/03/2016      Y
555283      280376335      06/03/2016      N
2808783      263311971      12/08/2015      N
2808783      286971613      28/05/2016      N
2808783      365329629      13/04/2018      Y
2808783      365329641      13/04/2018      N
23399505      238892180      30/09/2014      N
23399505      292601967      27/07/2016      N


Any help would be great!
Thanks!!
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of B_rider

ASKER

This is great, exactly what I'm looking for, Great Job!!!!
Avatar of B_rider

ASKER

Solved Great Job SdStuber!! Lvl 75 here you come!