asked
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
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
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.
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
-- 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)
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)=@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
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
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
18 People are following this question.
