find available time slot

johnkainn
johnkainn used Ask the Experts™
on
I have a query with the parameters: @SId, @StartDate, @EndDate
I have a table MyTable with the columns  Id, UserId,SId, StartDate(datetime), EndDate(datetime)

I would like to return all UserId where SId=@SId
and where the user is not occupied between StartDate and EndDate.
How would be best to do that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hi
basically the best approach will be to create a table with numbers:

create nums (num smallint identity(1,1));
declare @i smallint
set @i = 0
while @i < 1000
begin
 insert into nums default values
 set @i = @I+1
end

now, all you need to do is generate the relevant dates and join to them:
with dates as ( select dateadd(day, num, @startDate) d from nums )
select
from dates t2 left outer join your_table t1
  on t2.d between t1.startDate and t2.endDate and sid = @sid
where t2.d between @startDate and @endDate
and t1.sid is null


or something like it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial