CODEWITHSUNDEEP
sqloracle-databasedateoracle12c
Francesco Simeoli
Francesco Simeoli

Reputation: 541

Record filtering with adjacent start-end date (sum 1 day to date in Oracle 12)

SCENARIO

For each customer I want to discard all the contracts with a continuation (one contract is closed and another one opens the following day - for the same customer)

SIMPLIFICATION OF TABLE STRUCTURE

CONTRACTS
    - ID (PK INTEGER)
    - CUSTOMER_ID (FK INTEGER NOT NULL)
    - VALID_FROM (DATE NOT NULL)
    - VALID_TO (DATE NULLABLE)

EXAMPLE DATA

ID|CUSTOMER_ID|VALID_FROM|VALID_TO
1|1|2018-01-01|2018-07-31
2|1|2018-11-01|NULL
3|2|2018-03-01|2018-04-30
4|2|2018-05-01|2018-11-30
5|3|2018-06-01|NULL

EXPECTED RESULT

ID|CUSTOMER_ID|VALID_FROM|VALID_TO
1|1|2018-01-01|2018-07-31
2|1|2018-11-01|NULL
4|2|2018-05-01|2018-11-30
5|3|2018-06-01|NULL

SQL

SELECT
  C.*
FROM CONTRACTS C
LEFT JOIN CONTRACTS C1 ON (C.CUSTOMER_ID=C1.CUSTOMER_ID AND *C.VALID_TO + 1 DAY*=C1.VALID_FROM)
WHERE C1.ID IS NULL

QUESTION

I have to change C.VALID_TO + 1 DAY, what is the right syntax in Oracle?

Upvotes: 0

Views: 52

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

You can either explicitly say you're adding days by using an interval type;

LEFT JOIN CONTRACTS C1
ON (C.CUSTOMER_ID=C1.CUSTOMER_ID AND C.VALID_TO + INTERVAL '1' DAY=C1.VALID_FROM)

or more simply use date arithmetic by just removing the word 'DAY' from your query:

SELECT
  C.*
FROM CONTRACTS C
LEFT JOIN CONTRACTS C1 ON (C.CUSTOMER_ID=C1.CUSTOMER_ID AND C.VALID_TO + 1=C1.VALID_FROM)
WHERE C1.ID IS NULL
ORDER BY C.CUSTOMER_ID, C.VALID_FROM;

        ID CUSTOMER_ID VALID_FROM VALID_TO  
---------- ----------- ---------- ----------
         1           1 2018-01-01 2018-07-31
         2           1 2018-11-01           
         4           2 2018-05-01 2018-11-30
         5           3 2018-06-01

As a bonus, two alternative approaches; rather than using a left join, use not exists:

SELECT
  C.*
FROM CONTRACTS C
WHERE NOT EXISTS (
  SELECT *
  FROM CONTRACTS C1
  WHERE C.CUSTOMER_ID=C1.CUSTOMER_ID AND C.VALID_TO + 1=C1.VALID_FROM
)
ORDER BY C.CUSTOMER_ID, C.VALID_FROM;

or use an inline view and an analytic lead() call so you only have to hit the table once:

SELECT ID, CUSTOMER_ID, VALID_FROM, VALID_TO
FROM (
  SELECT
    C.*,
    LEAD(VALID_FROM) OVER (PARTITION BY CUSTOMER_ID ORDER BY VALID_FROM) AS LEAD_VALID_FROM
  FROM CONTRACTS C
)
WHERE LEAD_VALID_FROM IS NULL OR VALID_TO + 1 != LEAD_VALID_FROM
ORDER BY CUSTOMER_ID, VALID_FROM;

Both get the same result with your sample data.

db<>fiddle demo of all four queries..

Upvotes: 2

Related Questions