CODEWITHSUNDEEP
sqloracle-database
beckham
beckham

Reputation: 125

How to Delete the records based upon Prev and Next rows and assign the date based upon certain conditions

This is my insert Statements for the Source data.

REM INSERTING into EXPORT_TABLE  
SET DEFINE OFF;  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDFREE','UPSELL',to_date('11-MAR-14 17:05:35','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-14 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-JUN-14 23:59:00','DD-MON-YY HH24:MI:SS'),92,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('11-MAR-14 17:05:35','DD-MON-YY HH24:MI:SS'),to_date('12-JUN-14 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('10-MAR-15 23:59:00','DD-MON-YY HH24:MI:SS'),271,73.78);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDFREE','EXPIRATION',to_date('12-JUN-14 01:26:26','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-14 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-JUN-14 23:59:00','DD-MON-YY HH24:MI:SS'),92,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','RENEWAL',to_date('11-MAR-15 01:23:01','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('10-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),365,99);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','CANCELLATION',to_date('11-MAR-15 03:11:09','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-15 23:59:00','DD-MON-YY HH24:MI:SS'),0,-99);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('16-MAR-15 10:49:34','DD-MON-YY HH24:MI:SS'),to_date('16-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('10-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),360,97.92);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','CANCELLATION',to_date('22-FEB-16 18:19:00','DD-MON-YY HH24:MI:SS'),to_date('16-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('22-FEB-16 23:59:00','DD-MON-YY HH24:MI:SS'),343,-4.61);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','NEW SUBSCRIPTION',to_date('23-FEB-16 13:08:05','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('22-FEB-18 23:59:00','DD-MON-YY HH24:MI:SS'),730,178);    
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','CANCELLATION',to_date('23-FEB-16 15:16:44','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 23:59:00','DD-MON-YY HH24:MI:SS'),0,-178);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDGWA','UPSELL',to_date('23-FEB-16 15:22:42','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('22-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),28,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDGWA','CANCELLATION',to_date('11-MAR-16 04:25:50','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),17,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('14-MAR-16 10:02:05','DD-MON-YY HH24:MI:SS'),to_date('14-MAR-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('13-APR-16 23:59:00','DD-MON-YY HH24:MI:SS'),30,8.41);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('11-APR-16 09:33:06','DD-MON-YY HH24:MI:SS'),to_date('14-APR-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('13-MAR-17 23:59:00','DD-MON-YY HH24:MI:SS'),333,90.59);

I have my source data as 

REG_ID  | PRODUCT_CD | EVENT_TYPE      | EVENT_DATE         | TERM_START_DATE    | TERM_END_DATE      | DAYS | AMT
--------+------------+-----------------+--------------------+--------------------+--------------------+------+--------
4VKMH   | GUIDFREE   | UPSELL          | 11-MAR-14 17:05:35 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 11-MAR-14 17:05:35 | 12-JUN-14 00:00:00 | 10-MAR-15 23:59:00 | 271  |   73.78  
4VKMH   | GUIDFREE   | EXPIRATION      | 12-JUN-14 01:26:26 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | RENEWAL         | 11-MAR-15 01:23:01 | 11-MAR-15 00:00:00 | 10-MAR-16 23:59:00 | 365  |   99     *
4VKMH   | GUIDPAID   | CANCELLATION    | 11-MAR-15 03:11:09 | 11-MAR-15 00:00:00 | 11-MAR-15 23:59:00 |   0  |  -99  
4VKMH   | GUIDPAID   | UPSELL          | 16-MAR-15 10:49:34 | 16-MAR-15 00:00:00 | 10-MAR-16 23:59:00 | 360  |   97.92  
4VKMH   | GUIDPAID   | CANCELLATION    | 22-FEB-16 18:19:00 | 16-MAR-15 00:00:00 | 22-FEB-16 23:59:00 | 343  |   -4.61   
4VKMH   | GUIDPAID   | NEW SUBSCRIPTION| 23-FEB-16 13:08:05 | 23-FEB-16 00:00:00 | 22-FEB-18 23:59:00 | 730  |  178  
4VKMH   | GUIDPAID   | CANCELLATION    | 23-FEB-16 15:16:44 | 23-FEB-16 00:00:00 | 23-FEB-16 23:59:00 |   0  | -178  
4VKMH   | GUIDGWA    | UPSELL          | 23-FEB-16 15:22:42 | 23-FEB-16 00:00:00 | 22-MAR-16 23:59:00 |  28  |    0  
4VKMH   | GUIDGWA    | CANCELLATION    | 11-MAR-16 04:25:50 | 23-FEB-16 00:00:00 | 11-MAR-16 23:59:00 |  17  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 14-MAR-16 10:02:05 | 14-MAR-16 00:00:00 | 13-APR-16 23:59:00 |  30  |    8.41  
4VKMH   | GUIDPAID   | UPSELL          | 11-APR-16 09:33:06 | 14-APR-16 00:00:00 | 13-MAR-17 23:59:00 | 333  |   90.59

This data is already sorted by REG_ID, EVENT_DATE, and TERM_START_DATE.

I am trying to generate this output from that:

REG_ID  | PRODUCT_CD | EVENT_TYPE      | EVENT_DATE         | TERM_START_DATE    | TERM_END_DATE      | DAYS | AMT
--------+------------+-----------------+--------------------+--------------------+--------------------+------+--------
4VKMH   | GUIDFREE   | UPSELL          | 11-MAR-14 17:05:35 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 11-MAR-14 17:05:35 | 12-JUN-14 00:00:00 | 10-MAR-15 23:59:00 | 271  |   73.78  
4VKMH   | GUIDFREE   | EXPIRATION      | 12-JUN-14 01:26:26 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 16-MAR-15 10:49:34 | 16-MAR-15 00:00:00 | 22-FEB-16 23:59:00 | 360  |   97.92  
4VKMH   | GUIDPAID   | CANCELLATION    | 22-FEB-16 18:19:00 | 16-MAR-15 00:00:00 | 22-FEB-16 23:59:00 | 343  |   -4.61  
4VKMH   | GUIDGWA    | UPSELL          | 23-FEB-16 15:22:42 | 23-FEB-16 00:00:00 | 11-MAR-16 23:59:00 |  28  |    0  
4VKMH   | GUIDGWA    | CANCELLATION    | 11-MAR-16 04:25:50 | 23-FEB-16 00:00:00 | 11-MAR-16 23:59:00 |  17  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 14-MAR-16 10:02:05 | 14-MAR-16 00:00:00 | 13-APR-16 23:59:00 |  30  |    8.41  
4VKMH   | GUIDPAID   | UPSELL          | 11-APR-16 09:33:06 | 14-APR-16 00:00:00 | 13-MAR-17 23:59:00 | 333  |   90.59

This is the logic by which the result is derived from the original data:

For each record A with EVENT_TYPE 'RENEWAL', 'UPSELL', or 'NEW SUBSCRIPTION': if the following record B has EVENT_TYPE 'CANCELLATION', then:

  1. if record B has the same EVENT_DATE date part as A (ignore time), eliminate both record A and B from the result. So this is why records 4, 5, 8 and 9 are eliminated;
  2. else if record B has an earlier TERM_END_DATE value than record A, update A's TERM_END_DATE to that of B. So this is why record 10 has an updated TERM_END_DATE

I have tried to handle my 1st condition using the Following SQL and getting a issue ORA-00933: SQL command not properly ended

         (SELECT REG_ID, 
            EVENT_TYPE,
            EVENT_DATE,
            PRODUCT_CD,
            TERM_START_DATE,
            TERM_END_DATE,
            LAG(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_TYPE,
            LAG(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_DATE,
            LEAD(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_TYPE,
            LEAD(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_DATE  
            from    mytable)TEMP
            WHERE NOT (TEMP.event_type = 'CANCELLATION' AND (TEMP.PREV_EVENT_TYPE = 'NEW SUBSCRIPTION' OR TEMP.PREV_EVENT_TYPE  = 'RENEWAL' OR 
            TEMP.PREV_EVENT_TYPE = 'UPSELL') and TEMP.EVENT_DATE <> TEMP.PREV_EVENT_DATE)
            AND 
            NOT ((TEMP.PREV_EVENT_TYPE = 'NEW SUBSCRIPTION' OR TEMP.PREV_EVENT_TYPE  = 'RENEWAL' OR 
            TEMP.PREV_EVENT_TYPE = 'UPSELL') AND TEMP.EVENT_DATE <> TEMP.NEXT_EVENT_DATE AND TEMP.NEXT_EVENT_TYPE = 'CANCELLATION')

Upvotes: 0

Views: 110

Answers (2)

John Kuhns
John Kuhns

Reputation: 506

Let me state up front that I didn't test this in Oracle as I don't have an Oracle database handy.

I pared it down to a single join, it may be useful to compare performance with the accepted answer.

select  
    e1.reg_id,
    e1.product_cd,
    e1.event_type, 
    e1.event_date, 
    e1.term_start_date,
    case e1.event_type when 'CANCELLATION' then e1.term_end_date else coalesce(e2.term_end_date, e1.term_end_date) end as term_end_date,
    e1.days, 
    e1.amt
from event e1 
    left outer join event e2 on 
        e1.reg_id = e2.reg_id and 
        e1.product_cd = e2.product_cd and 
        e1.term_start_date = e2.term_start_date and 
        (e1.event_type = 'CANCELLATION' or e2.event_type = 'CANCELLATION') and 
        e1.event_date <> e2.event_date  
where trunc(e1.event_date) <> trunc(e2.event_date) or e2.reg_id is null

Upvotes: 1

trincot
trincot

Reputation: 350770

The reason why you got an error on your query is that before the sub-query is defined, you must indicate what you want to select from it. So if you had prefixed that with select * from it would have been a valid query.

Note that you don't have to do those or operations, as you can do that shorter with an in operator.

You should also negate some comparisons (as you already have NOT) and truncate dates with TRUNC.

Here is the query I would suggest:

SELECT      TEMP.REG_ID, 
            TEMP.EVENT_TYPE,
            TEMP.EVENT_DATE,
            TEMP.PRODUCT_CD,
            TEMP.TERM_START_DATE,
            CASE WHEN TEMP.EVENT_TYPE IN ('NEW SUBSCRIPTION', 'RENEWAL', 'UPSELL') 
                  AND TEMP.NEXT_EVENT_TYPE = 'CANCELLATION' THEN
                        LEAST(TEMP.TERM_END_DATE, TEMP.NEXT_TERM_END_DATE)
                 ELSE TEMP.TERM_END_DATE
            END AS TERM_END_DATE,
            TEMP.DAYS,
            TEMP.AMT
FROM    (SELECT     REG_ID, 
                    EVENT_TYPE,
                    EVENT_DATE,
                    PRODUCT_CD,
                    TERM_START_DATE,
                    TERM_END_DATE,
                    DAYS,
                    AMT,
                    LAG(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_TYPE,
                    LAG(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_DATE,
                    LEAD(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_TYPE,
                    LEAD(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_DATE,  
                    LEAD(TERM_END_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_TERM_END_DATE
            FROM    export_table) TEMP
WHERE   NOT (TEMP.EVENT_TYPE = 'CANCELLATION' 
             AND TEMP.PREV_EVENT_TYPE IN ('NEW SUBSCRIPTION', 'RENEWAL', 'UPSELL') 
             AND TRUNC(TEMP.EVENT_DATE) = TRUNC(TEMP.PREV_EVENT_DATE))
AND     NOT (TEMP.NEXT_EVENT_TYPE = 'CANCELLATION'
             AND TEMP.EVENT_TYPE IN ('NEW SUBSCRIPTION', 'RENEWAL', 'UPSELL') 
             AND TRUNC(TEMP.NEXT_EVENT_DATE) = TRUNC(TEMP.EVENT_DATE))

Note that the term_end_date of record 6 is also modified, as rule 2 is applicable to it.

Upvotes: 1

Related Questions