0

I want usernames from ind_emp_leaves table who are employers leave applied between same dates include from date and to date.

ind_emp_leaves table

username  Leavefromdate   leavetodate
--------  -------------    ---------
nagarajan   20-JUN-13      21-JUN-13
dhinesh     05-SEP-13      08-SEP-13
raju        08-SEP-13      11-SEP-13

In above table dhinesh and raju leave applied in same dates(between dates one date include it also be considered) and nagarajan applied different dates, so i want usernames of dhinesh and raju.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
appu
  • 1
  • 4

1 Answers1

1

You can look for rows that have overlapping dates using an exists clause:

select * from ind_emp_leaves iel
where exists (
  select 1 from ind_emp_leaves iel2
  where not (iel2.leavetodate < iel.leavefromdate
  or iel2.leavefromdate > iel.leavetodate)
  and iel2.rowid != iel.rowid
);

USERNAME   LEAVEFROMDATE LEAVETODATE
---------- ------------- -----------
dhinesh    05-SEP-13     08-SEP-13   
raju       08-SEP-13     11-SEP-13

Or if you want to know about the overlap, not just that it exists, you can use an outer join:

select iel.username, iel.leavefromdate, iel.leavetodate,
  iel2.username as overlaps
from ind_emp_leaves iel
left join ind_emp_leaves iel2
on not (iel2.leavetodate < iel.leavefromdate
  or iel2.leavefromdate > iel.leavetodate)
  and iel2.rowid != iel.rowid
where iel2.username is not null;

USERNAME   LEAVEFROMDATE LEAVETODATE OVERLAPS 
---------- ------------- ----------- ----------
raju       08-SEP-13     11-SEP-13   dhinesh    
dhinesh    05-SEP-13     08-SEP-13   raju

In both cases, the join is looking for different rows (because rowid doesn't match), where the date range is not entirely outside the main row you're looking at.

SQL Fiddle.

For psaraj12; if you have two date ranges, there are a number of ways they can overlap, so it's simpler sometimes to look at when they cannot do so. They do not overlap if one range is completely outside the other. That is, the 'to' date of either range is before the 'from' date of the other.

If you look at the first two rows from the question, they do not overlap because '21-Jun-13 < 05-Sep-13' is true. If you compare the second and third rows, '08-Sep-13 < 08-Sep-13' is false, so they do overlap.

So the logic in iel2.leavetodate < iel.leavefromdate or iel2.leavefromdate > iel.leavetodate is identifying where one range is completely outside the other, and the two ranges do not overlap. Since we want to know when they do, the whole expression is negated with the not (...).

Logically this is the same as saying where iel2.leavetodate >= iel.leavefromdate and iel2.leavefromdate <= iel.leavetodate, if that's easier to visualise.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • I would like to know how "where not" works,Kindly explain your answer – psaraj12 Sep 08 '13 at 15:42
  • @psaraj12 - I've added an explanation, which may or may not help. I found [this](http://stackoverflow.com/a/325964/266304) too, and an [external link](http://www.oracle-base.com/articles/misc/overlapping-date-ranges.php) that shows the same kind of idea. – Alex Poole Sep 09 '13 at 10:19
  • thanks for the explanation Alex and the links, i have a question for example if i have two sets of data with row1:-4th September to 6th september and row2:-3rd september to 9th September then the results would be wrong , how do we handle it ?? http://sqlfiddle.com/#!4/f5199/1 – psaraj12 Sep 10 '13 at 16:46
  • @psaraj12 - why would the results be wrong? You'd expect that to be reported as an overlap, right? [Like this](http://sqlfiddle.com/#!4/543fa/1), or using your example and the second version, [this shows all the overlaps]. I don't see the problem, so I'm not sure what you're getting at. If you can come up with a scenario you don't understand, it would be better to create a reproducible test case and ask a separate question. – Alex Poole Sep 10 '13 at 16:46
  • @psaraj12 - that's not how I read it, just that comparison needs to be inclusive of the start and end dates, and needs to find any overlaps. The OP will need to clarify if that's not the case. – Alex Poole Sep 10 '13 at 16:58
  • Sorry Alex i was confused since OP has mentioned same dates so i literally took the dates but now it is clear – psaraj12 Sep 10 '13 at 16:59