1

Using the following screenshot to show an example of the data set,

int,datetime2,datetime2, int

How can i return all the free periods of time which are available within the start and stop ranges which are also equal or longer than the slot duration parameter when they are not stored in the database as appointments

declare @startRange datetime2
declare @endRange datetime2
declare @slotDurationInMinutes int

set @startRange = '2016-06-06T22:00:00.000Z'
set @endRange = '2016-06-07T21:59:00.000Z'
set @slotDurationInMinutes = 30


-- from this appointment dataset how do i query for all the free periods which are as long or longer than the slotduration parameter
-- these values are not stored in the table?
select TSO_Table_ID, time_start, time_end, duration from Org_TSO_Table

enter image description here

For example the expected output of a query where the slotduration param is 30 minutes would be:

free_from=2016-06-06T22:00:00.000Z free_until=2016-06-06T22:00:30.000Z

(This record contains the search range start value)

free_from=2016-06-06T22:01:30.000Z free_until=2016-06-06T22:04:00.000Z

free_from=2016-06-06T22:04:20.000Z free_until=2016-06-06T22:10:00.000Z

free_from=2016-06-06T22:11:00.000Z free_until=2016-06-06T22:11:30.000Z

free_from=2016-06-06T22:12:30.000Z free_until=2016-06-07T21:59:00.000Z

(This record contains the search range end value)

Dizzle
  • 1,026
  • 13
  • 26
  • 2
    What determines a free period? There isn't enough information in the question to answer. Please read [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – Chris Pickford Jun 07 '16 at 09:17
  • a free period is a period of time which is not taken up by an appointment start and stop, hopefully this clarifies things. – Dizzle Jun 07 '16 at 09:20
  • What have you tired to far? – David Rushton Jun 07 '16 at 10:59

1 Answers1

2

It's hard to say if the solution below will work for you, given the small data sample, but hopefully it will get you started. 

IF OBJECT_ID('tempdb..#sked') is not null
DROP TABLE #sked

IF OBJECT_ID('tempdb..#tmpResults') is not null
DROP TABLE #tmpResults

create table #sked(
    ID int,
    time_start datetime2,
    time_end datetime2,
    duration int)

insert into #sked (ID,time_start,time_end,duration) values
(4,'2016-06-06 00:30:00','2016-06-06 01:30:00',3600000),
(2,'2016-06-06 04:00:00','2016-06-06 04:20:00',1200000),
(1,'2016-06-06 10:00:00','2016-06-06 11:00:00',3600000),
(6,'2016-06-06 11:30:00','2016-06-06 12:30:00',3600000)

declare @startRange datetime2
declare @endRange datetime2
declare @slotDurationInMinutes int

set @startRange = '2016-06-05T00:00:00.000'
set @endRange = '2016-06-07T21:59:00.000'
set @slotDurationInMinutes = 30

select
    time_end as free_from,
    isnull(lead(time_start) over (order by time_end),@endRange) as free_until
into #tmpResults
from
    #sked
where
    time_end >= @startRange 
    and time_end <= @endRange
    --and duration <= (@slotDurationInMinutes * 60000) --conversion to milliseconds

union all

select
    case when @startRange < min(time_start) then @startRange end as free_from,
    case when @startRange < min(time_start) then min(time_start) end as free_until
from
    #sked
where
    time_end >= @startRange 
    and time_end <= @endRange
    --and duration <= (@slotDurationInMinutes * 60000) --conversion to milliseconds
order by
    free_from

select 
    *,
    DATEDIFF(minute,free_from,free_until) 
from 
    #tmpResults
where
    free_from is not null
    and DATEDIFF(minute,free_from,free_until) >= @slotDurationInMinutes
S3S
  • 24,809
  • 5
  • 26
  • 45
  • Hi thanks for taking the time to reply, It looks kind of close, – Dizzle Jun 07 '16 at 18:03
  • No worries at all. Let me know if I can improve it to work for you. It really just depends on how you want the parameters to work, and if there would be conflicts of multiple schedules. i.e. your data set seems like this would be for only 1 entity, but if you were looking over multiple entities (people, scheduling blocks, etc) it likely wouldn't produce the needed results. But if this worked for you and is your answer then awesome. – S3S Jun 07 '16 at 18:06
  • Sorry the comment timed out as i was typing: Hi thanks for taking the time to reply, It looks kind of close, I get the following output: 2016-06-06 01:30:00.0000000 2016-06-06 04:00:00.0000000 2016-06-06 04:20:00.0000000 2016-06-06 10:00:00.0000000 2016-06-06 11:00:00.0000000 2016-06-06 11:30:00.0000000 2016-06-06 12:30:00.0000000 2016-06-07 21:59:00.0000000 But its not quite correct, for example if i change startRange to startRange = '2016-06-05T00:00:00.000' and the slot minutes to 30 I would expect a result of 2016-06-06 00:00.000 to 2016-06-06 00:30.00 be returned – Dizzle Jun 07 '16 at 18:09
  • @Dizzle I have updated the code to take in this logic to get a free time that starts before the first appointment. I am not sure how you want to filter on the `@slotDurationInMinutes` so that part is commented out, but you can add it in however you want. Also, if you don't want to get a `NULL` row for when the `UNION` isn't needed then you can wrap this in a logical test, or just filter it out afterwards. – S3S Jun 07 '16 at 18:26
  • hi its super close, I am trying to filter on the minutes using something like this: "and DATEDIFF(mi,free_until, free_from) >= @slotDurationInMinutes" for example if i were to filter for 10 minute slots the first entry should still be a result of 2016-06-06 00:00.000 to 2016-06-06 00:30.00 as thats 30 minutes in duration, I get the errors "Invalid column name for both of those fields" – Dizzle Jun 07 '16 at 18:34
  • no sweat, i edited it to make it work for you, but glad you got it on your end. Glad you can mark it complete! – S3S Jun 07 '16 at 18:41
  • yep will do all working nicely thought i spotted a bug but works really good (y) – Dizzle Jun 07 '16 at 18:42