Query to list records while Comparing Date1 of Next record with Date2 of Current Record

Question

Let say I am having "Employee_Sal" table with following columns:

Employe_Sal ( wage_No, From_Date, To_Date, Amt)

No  | From_Date |    TO_Date |   AMT 
1 ____01/7/2015  ____25/7/2015___40000
2 ____26/7/2015  ____05/8/2015___38000
3 ____03/8/2015  ____12/8/2015___59000

So here, I want to list out those two record which are like 2nd and 3rd here -

Next records From_Date should be LESS THAN Current Records TO_DATE

if we compare Record 1 and 2 ---- its fine

if we compare record 2 and 3 ----- 5/8/2015 < 3/8/2015 ----- IS FALSE

--- So I want to find such records using SQL Query.

Any Suggestions or Any Help, Please help.

EDITED:

I want to compare my record with my NEXT RECORD ONLY, not with the all records in the Table.

Which dbms product are you using? (date/time is too often far from ANSI SQL compliant...) — jarlh, Jul 03 '15 at 07:48
Actually, you really want to store `From_Date` to be **equal** to `To_Date`. [Dates are a contiguous range type, and should use an exclusive upper-bound](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). Why? Because you are paid the given rate _until_ the end date is reached. It also allows fun tricks like designing your table in such a way that this particular problems don't happen (each row grabs the `From_Date` of the next to use as `To_Date` - not always applicable, though). — Clockwork-Muse, Jul 03 '15 at 08:03
Duplicate of [Checking a table for time overlap?](http://stackoverflow.com/questions/6571538/checking-a-table-for-time-overlap) (That answer uses MySQL, and the `Time` type, but the answer should work directly). For more explanation of _why_ it works, [read this answer](http://stackoverflow.com/questions/143552/comparing-date-ranges/143568#143568). — Clockwork-Muse, Jul 03 '15 at 08:20
@Clockwork-Muse - This compares with each record in the table. I want to compare Two dates of RecordNo-X and RecordNo-X+1 , where X will be 0 to No_of_records in the table. — Salesforce Steps, Jul 03 '15 at 08:49
... how are you getting each `RecordNo`? How do you use it? Most solutions have problems, and aren't necessary in the first place - in this case it might be considered derived information, ordered based off the starting date. How sure are you that _only_ the next row might have problems? — Clockwork-Muse, Jul 03 '15 at 08:58

jarlh · Answer 1 · 2015-07-03T08:46:07.153

0

If there are no wage_No gaps guaranteed, try a self join, where you have t1 as "current record", and t2 as "next record":

select t1.*
from Employee_Sal t1
  join Employee_Sal t2
  on t1.wage_No = t2.wage_No - 1
where t2.From_Date < t1.TO_DATE

A gap safe way, use a correlated sub-select to find next wage_No:

select t1.*
from Employee_Sal t1
  join Employee_Sal t2
  on t2.wage_No = (select min(wage_No) from Employee_Sal t3
                   where t3.wage_No > t1.wage_No)
where t2.From_Date < t1.TO_DATE

Or, if no later wage_No is allowed to have a too early From_Date, do a NOT EXISTS:

select t1.*
from Employee_Sal t1
where not exists (select 1 from Employee_Sal t2
                  where t2.wage_No > t1.wage_No
                    and t2.From_Date < t1.TO_DATE)

edited Jul 03 '15 at 08:46

answered Jul 03 '15 at 07:57

jarlh

42,561
8
45
63

Unless you know _explicitly_ that something is a guaranteed sequential, no-gap, updated index (ie, something directly from `ROW_NUMBER()` in a statement), please don't suggest something like this. The problem is that auto-gen ids don't work this way... except in testing. It gets worse depending on whether there's auditing history in the table (so there's a row that matches, but it's no longer relevant), or was pulled from it. – Clockwork-Muse Jul 03 '15 at 08:15
Yes, I've already realized I was a bit too lazy here... Will add another answer. – jarlh Jul 03 '15 at 08:16
... actually, this brings up a different problem; are we actually guaranteed that it's the **next** record with the overlap? Granted, it should still fail one of the records anyways... – Clockwork-Muse Jul 03 '15 at 08:24
I suppose OP still has some design to do... But if that's the case, a simple NOT EXISTS would do! I'll add that too. – jarlh Jul 03 '15 at 08:43

Query to list records while Comparing Date1 of Next record with Date2 of Current Record

1 Answers1