SQL Question
How to adjust the dates and distinct records based on a column
REG_ID| EVENT_TYPE_CD | EVENT_DATE | PACKAGE_DESC |PRODUCT_TYPE|TERM_START_DATE|TERM_END_DATE
------|------------------|------------|-----------------|------------|---------------|----------
11156 | NEW SUBSCRIPTION | 23-FEB-16 | CONNECTED CARE |PAID | 23-FEB-16 | 23-FEB-16
11156 | CANCELLATION | 23-FEB-16 | CONNECTED CARE |PAID | 23-FEB-16 | 23-FEB-16
11156 | UPSELL | 23-FEB-16 | CONNECTED CARE |GOODWILL | 23-FEB-16 | 22-MAR-16
11156 | CANCELLATION | 11-MAR-16 | CONNECTED CARE |GOODWILL | 23-FEB-16 | 11-MAR-16
11156 | UPSELL | 14-MAR-16 | CONNECTED CARE |GOODWILL | 14-APR-16 | 13-APR-17
11156 | EXPIRATION | 14-APR-16 | CONNECTED CARE |GOODWILL | 14-MAR-16 | 13-APR-17
11163 | UPSELL | 23-FEB-16 | CONNECTED CARE |PAID | 23-FEB-16 | 23-FEB-16
11163 | CANCELLATION | 23-FEB-16 | CONNECTED CARE |PAID | 23-FEB-16 | 22-MAR-16
17215 | NEW SUBSCRIPTION | 18-JAN-16 | CONNECTED CARE |TRIAL | 18-JAN-16 | 17-JAN-17
17215 | NEW SUBSCRIPTION | 18-JAN-16 | GUIDANCE |TRIAL | 18-JAN-16 | 17-APR-16
17215 | CANCELLATION | 22-FEB-16 | GUIDANCE |TRIAL | 18-JAN-16 | 22-FEB-16
17215 | UPSELL | 25-FEB-16 | GUIDANCE |GOODWILL | 25-FEB-16 | 24-APR-16
17215 | EXPIRATION | 25-APR-16 | GUIDANCE |GOODWILL | 25-FEB-16 | 24-APR-16
17215 | NEW SUBSCRIPTION | 18-JAN-16 | REMOTE |TRIAL | 18-JAN-16 | 17-APR-16
17215 | UPSELL | 25-FEB-16 | REMOTE |GOODWILL | 25-FEB-16 | 24-APR-16
17215 | NEW SUBSCRIPTION | 18-JUN-16 | REMOTE |PAID | 18-JUN-16 | 17-JUL-16
17215 | UPSELL | 25-JUL-16 | REMOTE |GOODWILL | 25-JUL-16 | 24-AUG-16
The output I need is that everything needs to be sorted by EVENT_DATE (Series of Events)
- if a 'Goodwill' EVENT_DATE follows a 'Trial' product EVENT_DATE
then treat this as 'Trial'. If a 'Goodwill' EVENT_DATE follows a
'Paid' product EVENT_DATE then treat this as 'Paid' and adjust the
TERM_END_DATE (Example for REMOTE Ideal scenario when there is no
Cancellation or EXPIRATION event_type_cd for particular PACKAGE_DESC
in a REG_ID) - If there is a Event After the Cancellation then ignore Cancellation (11163 shows up: that's the reason since there is no new
event after the cancellation) - If there are multiple Goodwill's
following the trail we need to take the maximum TERM_END_DATE in the
cycle needs to assigned as TERM_END_DATE (REG_ID 17215 and GUIDANCE). - EXPIRATION RECORD should always be there and its term_start_date
needs to get adjusted to the Term_start_date of the Cycle's 1st
record.
REG_ID| EVENT_TYPE_CD | EVENT_DATE | PACKAGE_DESC |PRODUCT_TYPE|TERM_START_DATE| TERM_END_DATE
------|------------------|------------|-----------------|------------|---------------|----------
11156 | NEW SUBSCRIPTION | 23-FEB-16 | CONNECTED CARE |PAID | 23-FEB-16 | 13-APR-17
11156 | EXPIRATION | 14-APR-16 | CONNECTED CARE |PAID | 23-FEB-16 | 13-APR-17
11163 | UPSELL | 23-FEB-16 | CONNECTED CARE |PAID | 23-FEB-16 | 23-FEB-16
11163 | CANCELLATION | 23-FEB-16 | CONNECTED CARE |PAID | 23-FEB-16 | 22-MAR-16
17215 | NEW SUBSCRIPTION | 18-JAN-16 | CONNECTED CARE |TRIAL | 18-JAN-16 | 17-JAN-17
17215 | NEW SUBSCRIPTION | 18-JAN-16 | GUIDANCE |TRIAL | 18-JAN-16 | 24-APR-16
17215 | EXPIRATION | 25-APR-16 | GUIDANCE |TRAIL | 18-JAN-16 | 24-APR-16
17215 | NEW SUBSCRIPTION | 18-JAN-16 | REMOTE |TRIAL | 18-JAN-16 | 24-APR-16
17215 | NEW SUBSCRIPTION | 18-JUN-16 | REMOTE |PAID | 18-JUN-16 | 24-AUG-16
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
The rules are quite extensive, and your might get better results and performance with PL/SQL code, as that can use variables while iterating over a cursor.
Still, I think the following query may do what you need:
select reg_id,
event_type_cd,
event_date,
package_desc,
case product_type when 'GOODWILL' then coalesce(prev_product_type, 'TRIAL')
else product_type
end as product_type,
case event_type_cd when 'EXPIRATION' then first_term_start_date
else term_start_date
end as term_start_date,
case next_product_type when 'GOODWILL' then next_term_end_date
else term_end_date
end as term_end_date
from (select reg_id,
event_type_cd,
event_date,
package_desc,
product_type,
term_start_date,
term_end_date,
first_value(term_start_date) over (
partition by reg_id, package_desc
order by event_date, term_end_date, event_type_cd desc) as first_term_start_date,
lead(term_end_date, 1) over (
partition by reg_id, package_desc
order by event_date, term_end_date, event_type_cd desc) as next_term_end_date,
lag(product_type, 1) over (
partition by reg_id, package_desc
order by event_date, term_end_date, event_type_cd desc) as prev_product_type,
lead(product_type, 1) over (
partition by reg_id, package_desc
order by event_date, term_end_date, event_type_cd desc) as next_product_type
from (select reg_id,
event_type_cd,
event_date,
package_desc,
product_type,
term_start_date,
term_end_date,
lead(product_type, 1, '-') over (
partition by reg_id, package_desc
order by event_date, term_end_date, event_type_cd desc) as next_product_type
from mytable)
where not (event_type_cd = 'CANCELLATION' and next_product_type <> '-')
and not (product_type = 'GOODWILL' and next_product_type = 'GOODWILL')
)
where not (product_type = 'GOODWILL' and event_type_cd <> 'EXPIRATION' and prev_product_type is not null)
order by reg_id, package_desc, event_date, term_end_date, event_type_cd desc
The query has a two-level nested sub query.
The inner-most query only serves to get the product_type of the next record within the cycle (i.e. within the same partition of reg_id and package_desc).
The middle query uses that information to eliminate:
- 'CANCELLATION' records, unless they are the very last record of their cycle;
- Consecutive 'GOODWILL' records, leaving only the last in place of every sequence -- this is last one is temporary, but is at this stage still needed;
The middle query also re-fetches the product_type of the next record in the cycle, as it might now have changed due to the eliminated records. Additionally, it determines:
- the term_start_date of the first record in the cycle;
- the term_end_date of the next record in the cycle;
- the product_type of the previous record in the cycle;
Finally, the outer query uses this information to:
- set the product_type to that of the previous record (or 'TRIAL' if there is no previous) if it concerns a 'GOODWILL' record;
- set the term_start_date to the that of the first record in the cycle, if it concerns an 'EXPIRATION' record;
- set the term_end_date to that of the next record in the cycle, if the next record concerns a 'GOODWILL' record.
The 'GOODWILL' records (before the change in the first bullet above) are excluded from the result, except when they relate to a first record in their cycle, or correspond to an 'EXPIRATION' record.
The order by clause uses the order as you mentioned in comments, with an additional event_type_cd desc to make sure "The EVENT_TYPE_CD's for Cancellation or Expiration will always follow the New Subscription or UPSELL for a particular REG_ID, PACKAGE_DESC". This is because by fortune both 'NEW SUBSCRIPTION' and 'UPSELL' come both later in the alphabetical order than 'CANCELLATION' and 'EXPIRATION', so in descending order we get them sorted right.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download