CODEWITHSUNDEEP
sqlsql-server-2008
miguelSnam
miguelSnam

Reputation: 73

sql get available records between two hours

I have a table with the following values ​​and I need to display the names that are on schedule available

Name | EntryTime|ExitTime  |BreakStart |BreakEnd 

David| 06:00    | 18:00    | 06:30     | 8:00 |
Kim  | 18:00    | 06:00    | 20:00     | 21:00|
Jess | 06:30    | 15:00    | 12:00     | 13:00|
Mike | 18:00    | 06:00    | 19:00     | 20:00|

For example: 

If the hours is 20:30
   the query return Kim
If the hours is 11:00
   the query return David, Jess

I have this but don't work when i need return the name of Kim or Mike

SELECT Name FROM employees 
WHERE (EntryTime > @time AND ExitTime < @time)
AND (@time not between  BreakStart AND BreakEnd)

Upvotes: 1

Views: 92

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

From the example with "Kim", you don't seem to care about breaks. I think the following is the logic you want:

SELECT Name
FROM employees 
WHERE (EntryTime < ExitTime and @time between EntryTime and ExitTime) or
      (ExitTime < EntryTime and @time not between ExitTime and EntryTime);

I generally don't advocate using `between with dates and times. In this case, it makes the logic readable.

Upvotes: 1

Related Questions