oracle的complex,Oracle 18c - Complex sql

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.

# Answer 1

Test for all cases:

with t(emp_id, emp_flag, date_1, date_2, create_date) as (

select 101, 'Y', null, null, date '2018-01-18' from dual union all

select 101, 'Y', date '2019-02-01', null, date '2019-02-15' from dual union all

select 101, 'Y', date '2019-02-01', date '2021-02-12', date '2019-02-16' from dual union all

select 102, 'Y', null, null, date '2018-01-18' from dual union all

select 102, 'Y', date '2019-02-10', null, date '2019-02-15' from dual union all

select 102, 'Y', date '2019-02-11', date '2021-02-12', date '2019-02-16' from dual union all

select 103, 'Y', null, null, date '2018-01-18' from dual union all

select 103, 'Y', date '2019-02-10', null, date '2019-02-15' from dual union all

select 103, 'Y', date '2019-02-10', null, date '2019-02-15' from dual union all

select 103, 'Y', date '2019-02-11', date '2021-02-21', date '2020-12-02' from dual )

select emp_id, emp_flag, date_1, date_2, create_date

from (

select emp_ID, emp_flag, date_1, date_2, create_date,

lag(date_1) over (partition by emp_id order by create_date) prev_dt1

from t )

where date_1 <> nvl(prev_dt1, date_1 - 1);

Result:

EMP_ID EMP_FLAG DATE_1 DATE_2 CREATE_DATE

---------- -------- ----------- ----------- -----------

101 Y 2019-02-01 2019-02-15

102 Y 2019-02-10 2019-02-15

102 Y 2019-02-11 2021-02-12 2019-02-16

103 Y 2019-02-10 2019-02-15

103 Y 2019-02-11 2021-02-21 2020-12-02

Edit:

when there are more than one records with no change in Date_1. It

should not return a record for that Emp_id

In this case date_1 is set in first row (id 104). If you want hide rows in such case use:

with t(emp_id, emp_flag, date_1, date_2, create_date) as (

select 104, 'Y', date '2019-02-10', null, date '2019-02-15' from dual union all

select 104, 'Y', date '2019-02-10', null, date '2019-02-16' from dual union all

select 105, 'Y', date '2019-02-10', null, date '2019-02-15' from dual union all

select 105, 'Y', null, null, date '2019-02-16' from dual )

select emp_id, emp_flag, date_1, date_2, create_date

from (

select emp_ID, emp_flag, date_1, date_2, create_date,

lag(date_1) over (partition by emp_id order by create_date) prev_dt1,

row_number() over (partition by emp_id order by create_date) rn

from t )

where (date_1 is not null and prev_dt1 is null and rn > 1)

or date_1 <> prev_dt1

or date_1 is null and prev_dt1 is not null;

I also added case when previous date was set and now it is null (id 105). If it is not possible or you don't want it then remove last row.

# Answer 2

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

# Answer 3

You can use the lag function instead of lead here:

with tableA as

(

select 456 as Emp_ID,'Y' as Emp_flag,CAST(NUll as date) as Date_1,CAST(NULL as date) as Date_2,CAST('18Jan2018' as date) as Create_date from dual union

select 456,'Y',CAST('01Oct2019' as date),Null,CAST('15Feb2019' as date) from dual union

select 456,'Y',CAST('02Nov2019' as date),CAST('12Feb2021' as date),CAST('12Feb2020' as date) from dual)

select x.Emp_ID,x.Emp_flag,x.Date_1,x.Date_2,x.Create_date

from

(select a.*

,lag(a.date_1) Over (partition by a.Emp_ID order by a.create_date) as lag_date

from tableA a) x

where x.date_1 is not null and x.date_1<>COALESCE(x.lag_date,CAST('01Jan2100' as date))

This will give out the values only when there is a change in date_1. Since NULL comparisons won't work, I have replace them with 1/1/2100. Hope this helps.

Edit:

I checked for a sample like you mentioned and it does seem to be working. If it's not working, kindly share the expected and the result you are getting:

with tableA as

(

select 456 as Emp_ID,'Y' as Emp_flag,CAST(NUll as date) as Date_1,CAST(NULL as date) as Date_2,CAST('18Jan2018' as date) as Create_date from dual union

select 456,'Y',CAST('01Oct2019' as date),Null,CAST('15Feb2019' as date) from dual union

select 456,'Y',CAST('01Oct2019' as date),CAST('12Feb2021' as date),CAST('12Feb2020' as date) from dual)

select x.Emp_ID,x.Emp_flag,x.Date_1,x.Date_2,x.Create_date

from

(select a.*

,lag(a.date_1) Over (partition by a.Emp_ID order by a.create_date) as lag_date

from tableA a) x

where x.date_1 is not null and x.date_1<>COALESCE(x.lag_date,CAST('01Jan2100' as date))