beckham beckham - 4 months ago 15
SQL Question

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')

Answer

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.