sql - Oracle 18c - Complex sql

Question

I have a table with following columns:

Emp_ID Number
Emp_flag Varchar2(1)
Date_1 Date
Date_2 Date
create_date Date

No PK on this table , there are many records with duplicates of Emp_id..

What I need to know, is when a new Date_1 is entered (so Null to a date, or from Date 1 to Date 2) on what date that happened.

I can’t just look at a single record to compare Date_1 with create_date because there are many times in the many records for a given Emp_ID when the Date_1 is simply “copied” to the new record. A Date_1 may have been originally entered on 02/15/2019 with a value of 02/01/2019. Now let’s say Date_2 gets added on 02/12/2020. So the table looks like this:

Emp_ID  Emp_flag    Date_1     Date_2      Create_Date
123     Y           Null       Null        1/18/2018
123     Y           02/1/2019  Null        02/15/2019
123     Y           02/1/2019  02/12/2021  02/12/2020

I need a SQL query that would tell me that Emp_ID 123 had a Date_1 of 02/1/2019 entered on 02/15/2019 and NOT pick up any other record.

Expected output:

Emp_ID  Emp_flag    Date_1     Date_2      Create_Date
123     Y           02/1/2019  Null        02/15/2019

Example 2 (notice date_1 is different):

Emp_ID  Emp_flag    Date_1     Date_2      Create_Date
456     Y           Null       Null        1/18/2018
456     Y           10/1/2019  Null        02/15/2019
456     Y           11/2/2019  02/12/2021  02/12/2020

Expected output:

Emp_ID  Emp_flag    Date_1     Date_2      Create_Date
456     Y           10/1/2019  Null        02/15/2019
456     Y           11/2/2019  02/12/2021  02/12/2020

Example 3:

Emp_ID  Emp_flag    Date_1     Date_2      Create_Date
456     Y           Null       Null        1/18/2018
456     Y           10/1/2019  Null        02/15/2019
456     Y           10/1/2019  Null        02/15/2019
456     Y           11/2/2019  02/12/2021  02/12/2020

Expected output:

Emp_ID  Emp_flag    Date_1     Date_2      Create_Date
456     Y           10/1/2019  Null        02/15/2019
456     Y           11/2/2019  02/12/2021  02/12/2020

Example 4:

Emp_ID  Emp_flag    Date_1     Date_2      Create_Date
456     Y           10/1/2019  Null        02/15/2019
456     Y           10/1/2019  Null        02/16/2019

Expected output: No records.

标签： sqloracleoracle18c

Answer 1

You can use the Lag function to check whether the previous value of date_1 existed or not.

SELECT x.emp_id,
       x.date_1,
       x.create_date AS first_date_with_date_1
FROM (
    SELECT t.emp_id,
           t.create_date,
           t.date_1,
           LAG(t.date_1) OVER (PARTITION BY t.emp_id ORDER BY t.create_date) AS last_date_1
    FROM your_table t
) x
WHERE x.date_1 IS NOT NULL
  AND x.last_date_1 IS NULL