CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
user4293620
user4293620

Reputation: 3

LEAD Function - Oracle

I have a start date and an end_Date that I would like to compare:

example:

  1. start_date: 2017-12-13 00:00:00    End_date: 2017-12-13 09:44:00
  2. start_date: 2017-12-13 09:44:00    End_date: 2017-12-13 16:19:00
  3. start_Date: 2017-12-13 16:19:00    End_date: 2017-12-18 00:00:00

I would like to compare start_date line 2 with end_date line 1. If they dont match do something. The lead function below is not giving me what I want. Please chime in and help. As soon as my query is working I will put it a loop and go through the whole table. thanks

    SELECT start_Date, end_date,
      LEAD (start_Date, 1) OVER (ORDER BY start_Date) AS next_start_DAte
      from    my_table
      where   start_date >= '2017-12-01' 
      AND     END_DATE < '2018-08-01'
      AND     FILE_ID IS NULL
      ORDER BY UNIT_ID, START_DATE;

Upvotes: 0

Views: 1936

Answers (2)

Karthik
Karthik

Reputation: 99

You can use this following query,

SELECT START_DATE ,END_DATE, 
--If they dont match do something. 
case when  trunc(next_date) <> trunc(end_date) then 1 else 2 end dosomething 
FROM (
SELECT leadtest.*, lead(start_date, 1) over (order  by start_date) next_date FROM leadtest)
;

Hope this helps!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can just use not exists to find rows that do not match exactly with the "next" row:

select t.*
from my_table t
where not exists (select 1
                  from my_table t2
                  where t2.start_date = t.end_date
                 );

This will always return the last row of the table, but that row meets the conditions you have described.

Upvotes: 1

Related Questions