maybe its too simple or maybe its too complicated, but I cant find any solution around this problem. Or maybe i just spent too much time looking at the screen trying to solve it, but now i am officially out of all clues.

Say I have a SQL Table like this...

id  AideCode    StartTime           EndTime             
602     1017    2014-01-13 07:00:00 2014-01-13 19:00:00 
2442    1017    2014-01-14 07:00:00 2014-01-14 19:00:00 
4684    1017    2014-01-15 08:00:00 2014-01-16 08:00:00 
6569    1017    2014-01-16 08:00:00 2014-01-17 08:00:00 
8404    1017    2014-01-17 08:00:00 2014-01-18 08:00:00 
9968    1017    2014-01-18 08:00:00 2014-01-19 08:00:00
602     1018    2014-01-13 03:00:00 2014-01-13 13:00:00 
602     1018    2014-01-14 10:00:00 2014-01-13 18:00:00

Now I want to find out if anyone is available on 2014-01-13 between 4am - 5 am, basiclly between '2014-01-13 04:00:00' and '2014-01-13 05:00:00', it should return 1017 because it is available between that time slot.

Also I want to find out if anyone is available on 2014-01-14 between 7am - 9 am, basiclly between '2014-01-14 07:00:00' and '2014-01-14 09:00:00', it should return 1018 because it is available between that time slot.

So what will be that magical sql query look

Try the below:

Select * From Tablename Where starttime <@startdate and endtime>@enddate

I doubt the below case, because, there is no data for that date between 4am-am...

2014-01-13 between 4am - 5 am, basiclly between '2014-01-13 04:00:00' and '2014-01-13 05:00:00', it should return 1017..

• Edited by Latheesh NKMicrosoft community contributor 10 hours 29 minutes ago
• Proposed as answer by HimanshuSharma 4 hours 8 minutes ago

Hello Saqib,

Last row has some invalid data I guess

602     1018    2014-01-14 10:00:00 2014-01-13 18:00:00

What I did not understand is, does the table shows filled time slots? I think so, am I right?

Please check following script

create table tblTimeSlots (id  int, AideCode    int, StartTime    datetime,       EndTime             datetime)

insert into tblTimeSlots values
(602     ,1017    ,'2014-01-13 07:00:00','2014-01-13 19:00:00'), 
(2442    ,1017    ,'2014-01-14 07:00:00','2014-01-14 19:00:00'), 
(4684    ,1017    ,'2014-01-15 08:00:00','2014-01-16 08:00:00'), 
(6569    ,1017    ,'2014-01-16 08:00:00','2014-01-17 08:00:00'),
(8404    ,1017    ,'2014-01-17 08:00:00','2014-01-18 08:00:00 '),
(9968    ,1017    ,'2014-01-18 08:00:00','2014-01-19 08:00:00'),
(602     ,1018    ,'2014-01-13 03:00:00','2014-01-13 13:00:00'), 
(602     ,1018    ,'2014-01-14 10:00:00','2014-01-14 18:00:00')


declare @startdate datetime
declare @enddate datetime
select @startdate = '2014-01-13 04:00:00', @enddate = '2014-01-13 05:00:00' -- 1017
--select @startdate = '2014-01-14 07:00:00', @enddate = '2014-01-14 09:00:00' -- 108

;with cte as (
	select distinct AideCode from tblTimeSlots
)
Select cte.AideCode
from cte 
left join tblTimeSlots t on cte.AideCode = t.AideCode
and (
	(@startdate <= t.starttime and t.endtime <= @enddate) OR
	(@startdate <= t.starttime and (@enddate >= t.starttime and @enddate <= t.endtime)) OR
	((@startdate >= t.starttime and @startdate <= t.endtime) and t.endtime <= @enddate) OR
	(t.starttime <= @startdate and @enddate <= t.endtime)
)
where t.AideCode is null