x

Getting 30 minute slots between time

I have a Start Datetime and a End Datetime.

Eg: 10:00 am - 12:00 pm

I have to create 4 slots between the, each ranging for 30 minutes.

Eg: 10:00 am-10:30 am 10:30 am-11:00 am

11:00 am-11:30 am 11:30 am-12:00 pm This 30 minutes can vary and is not a constant. I have tried a few things but they don't seem to work. Can someone please help. Thank you. I have tried this but i only get the slot difference not the slotfrom-slotto with cte as
(
select convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)) as DATE
union all
select c.DATE+'00:20:00' as QuarterTime
from cte c
where c.date
more ▼

asked Oct 28, 2015 at 07:04 AM in Default

avatar image

fashraf
538 17 21 29

Not finished but should be a good starting point

 declare @starttime datetime = '2015-10-28 10:00', @endtime datetime = '2015-10-28 12:00', @interval int = 30, @slots int
 
 select @slots = datediff(minute, @starttime, @endtime)/@interval
 
 SELECT TOP (@slots) N=IDENTITY(INT, 1, 1)
 INTO #Numbers
 FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;
 
 select 
     dateadd(minute,((n-1)*@interval),@starttime),
     dateadd(minute,(n*@interval),@starttime)
 from
     #numbers
 
 drop table #numbers
Oct 28, 2015 at 08:51 AM anthony.green

great.. what happens if Book a slot(inserting the from-to time in a separate column ) then can i hide the booked slot ?

Oct 28, 2015 at 09:04 AM fashraf

Yes you can, eg with some sort of WHERE-clause using NOT EXISTS to check if the slot is in the Bookings-table or not.

Oct 28, 2015 at 09:07 AM Magnus Ahlkvist

I take it you have a booking table? Just need to join back onto your booking table where there is no booking already for that slot. Or if this is going to generate your booking table, I take it you will have a "Booked" column, just need to filter out where Booked is not null

Oct 28, 2015 at 09:07 AM anthony.green
  • Make CTE ;WITH CTE (n) AS ( SELECT TOP (SELECT DATEDIFF(MINUTE,from_dt,to_dt)/30 FROM d) 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1) FROM sys.all_columns )

  • QUERY SELECT TSStart=DATEADD(minute, n, from_dt) ,TSEnd=DATEADD(minute, n + 30, from_dt) ,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, from_dt), 0) + ' - ' + CONVERT(VARCHAR(100), DATEADD(minute, n + 30, from_dt), 0) FROM d CROSS APPLY ( SELECT n FROM CTE WHERE n BETWEEN 0 AND DATEDIFF(minute, from_dt, DATEADD(minute, -30, to_dt))) a ORDER BY TSStart

and in my booking table I have from to.. now how do i exclude that time ? please help (really really appreciate it)

Oct 28, 2015 at 09:10 AM fashraf
show all comments (comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You could use a numbers table to generate the time slots.

Here's an example which I Think works ok.

The casts of to datetime and back to time is used to make sure we don't get repeated timeslots because the dateadd-functions spills over to 00:00:00.000.

 CREATE PROC GetTimeSlots(@starttime time, @endtime time, @interval int)
 AS
 DECLARE @startdate datetime = @starttime;
 DECLARE @enddate datetime = @endtime;
 --Get numbers from 0 to 3600
 WITH cte AS(
     SELECT 1 AS n
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL
     SELECT 1
     UNION ALL
     SELECT 1
 ), cte2 AS (
     SELECT TOP 3600 row_number() OVER(ORDER BY c1.n)-1 AS rownum
     FROM cte c1
     CROSS JOIN cte c2
     CROSS JOIN cte c3
     CROSS JOIN cte c4
 )
 SELECT 
     CAST(DATEADD(minute,rownum*@interval,@startdate) AS time) AS slotfrom,
     CAST(DATEADD(minute,(rownum+1)*@interval,@startdate) AS time) AS slotto
 FROM 
     cte2 
 WHERE 
     DATEADD(minute,(rownum+1)*@interval,@startdate)<=@enddate 
     AND
     DATEADD(minute,rownum*@interval,@startdate)>=@startdate 
 
 go
 EXEC GetTimeSlots '10:00','15:00',15;

EDIT Here's an alternative solution, using the benefit from Anthony Green's suggestion and using datetime instead of time as input parameters, to let the solution work over a period of Days. END EDIT

 ALTER PROC GetTimeSlots(@starttime datetime, @endtime datetime, @interval int)
 AS
 DECLARE @NumSlots int = DATEDIFF(minute,@starttime, @endtime) / @interval;
 --Get correct number of slots
 WITH cte AS(
     SELECT 1 AS n
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL 
     SELECT 1
     UNION ALL
     SELECT 1
     UNION ALL
     SELECT 1
 ), cte2 AS (
     SELECT TOP(@numslots) row_number() OVER(ORDER BY c1.n)-1 AS rownum
     FROM cte c1
     CROSS JOIN cte c2
     CROSS JOIN cte c3
     CROSS JOIN cte c4
     CROSS JOIN cte c5
 )
 SELECT 
     DATEADD(minute,rownum*@interval,@starttime) AS slotfrom,
     DATEADD(minute,(rownum+1)*@interval,@starttime) AS slotto
 FROM 
     cte2 
more ▼

answered Oct 28, 2015 at 08:46 AM

avatar image

Magnus Ahlkvist
22k 20 41 42

(comments are locked)
10|1200 characters needed characters left

Rough guess at a sample booking table

create table #booking (start datetime, [end] datetime) insert into #booking values ('2015-10-28 08:00','2015-10-28 08:30'), ('2015-10-28 10:00','2015-10-28 10:30'), ('2015-10-28 10:30','2015-10-28 11:00') declare @starttime datetime = '2015-10-28 08:00', @endtime datetime = '2015-10-28 12:00', @interval int = 30, @slots int

select @slots = datediff(minute, @starttime, @endtime)/@interval

SELECT TOP (@slots) N=IDENTITY(INT, 1, 1) INTO #Numbers FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns b;

select dateadd(minute,((n-1)@interval),@starttime) as start, dateadd(minute,(n@interval),@starttime) as [end] into #slots from #numbers

select s., b. from #slots s left join #booking b on s.start = b.start and s.[end] = b.[end] where b.start is null

drop table #numbers, #booking, #slots

more ▼

answered Oct 28, 2015 at 09:42 AM

avatar image

anthony.green
2.9k 1 4 6

(comments are locked)
10|1200 characters needed characters left

Rough guess at a sample booking table

 create table #booking (start datetime, [end] datetime)
 insert into #booking values 
 ('2015-10-28 08:00','2015-10-28 08:30'),
 ('2015-10-28 10:00','2015-10-28 10:30'),
 ('2015-10-28 10:30','2015-10-28 11:00')

 declare @starttime datetime = '2015-10-28 08:00', @endtime datetime = '2015-10-28 12:00', @interval int = 30, @slots int
 
 select @slots = datediff(minute, @starttime, @endtime)/@interval
 
 SELECT TOP (@slots) N=IDENTITY(INT, 1, 1)
 INTO #Numbers
 FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;
 
 select 
     dateadd(minute,((n-1)*@interval),@starttime) as start,
     dateadd(minute,(n*@interval),@starttime) as [end]
 into
     #slots
 from
     #numbers
 
 select s.*, b.* from #slots s
 left join #booking b
 on s.start = b.start and s.[end] = b.[end]
 where b.start is null
 
 drop table #numbers, #booking, #slots
more ▼

answered Oct 28, 2015 at 09:28 AM

avatar image

anthony.green
2.9k 1 4 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x88
x45
x38

asked: Oct 28, 2015 at 07:04 AM

Seen: 295 times

Last Updated: Oct 28, 2015 at 03:43 PM

Copyright 2017 Redgate Software. Privacy Policy