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!!
B_riderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can you post the expected results from that sample data?
B_riderAuthor Commented:
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!!
sdstuberCommented:
How about this?

I wasn't sure what your criteria was for changing the activity_dt.
Based on your sample data I assumed it was when the dup_record was N then look back 30 days to get the first date within that range - which could be itself.

So, this does produce the given expections for the given inputs, but might not be exactly what you were looking for.  If so, please expand the sample data and expected results with more examples.

  SELECT subscription_id,
         servreq_header_id,
         CASE WHEN dup_record = 'N' THEN first_act_dt ELSE activity_dt END activity_dt,
         dup_record
    FROM (SELECT subscription_id,
                 servreq_header_id,
                 activity_dt,
                 CASE
                     WHEN   LEAD(activity_dt)
                                OVER(PARTITION BY subscription_id ORDER BY servreq_header_id)
                          - activity_dt <=
                          30
                     THEN
                         'Y'
                     ELSE
                         'N'
                 END
                     dup_record,
                 FIRST_VALUE(
                     activity_dt
                 )
                 OVER(
                     PARTITION BY subscription_id
                     ORDER BY activity_dt
                     RANGE INTERVAL '30' DAY PRECEDING
                 )
                     first_act_dt
            FROM events e)
ORDER BY subscription_id, servreq_header_id

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
B_riderAuthor Commented:
This is great, exactly what I'm looking for, Great Job!!!!
B_riderAuthor Commented:
Solved Great Job SdStuber!! Lvl 75 here you come!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.