How to split time into hourly slot using SQL (can use view,or stored proc or fun
-
I have data in table which has start date, end date and duration. I want to show hourly time slot.
**logic**:
- Condition 1. If start date =9:00 and end date = 11:00 then show the date as
09:00-10:00
10:00-11:00
It should repeat 2 times and all related column data will also repeat 2 times.
this will continue if time slot is suppose 11:00- 14:00 then
11:00-12:00
12:00-13:00
13:00-14:00
It should repeat 3 times.
- Condition 2: If start date is 9:30 and end date is 10:30 then
time should round up. i.e. start date should be 9:00 and end date should be 11:00
How can I achieve this in Sql Server?
-
What are your data types?
What happens when the StartTime and FinishTime cross over midnight?
Are the time durations always in 60 minute segments?
-
Hi,
- Datatype: StartTime ,EndTime is Varchar(5)
- It is fixed hour slot : starts at 09:00 end at 18:00
09:00-10:00
10:00-11:00
till
17:00-18:00
- Yes, it should always be 60 duration.
Hope I answered your questions.
Regards,
-
I would start by creating a table of valid time slots
IF OBJECT_ID(N'dbo.Timeslots', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Timeslots;
END;
GO
CREATE TABLE dbo.Timeslots (
SlotStart time(0) NOT NULL
, SlotEnd AS CAST(DATEADD(hh, 1, SlotStart) AS time(0))
);
GO
INSERT INTO dbo.Timeslots ( SlotStart )
VALUES ( '08:00:00' ), ( '09:00:00' ), ( '10:00:00' ), ( '11:00:00' ), ( '12:00:00' ), ( '13:00:00' )
, ( '14:00:00' ), ( '15:00:00' ), ( '16:00:00' ), ( '17:00:00' ), ( '18:00:00' ), ( '19:00:00' );
GOThen you can join to the lookup table to get the data broken down by the time slots.
CREATE TABLE #SourceData (
SlotId int NOT NULL
, StartTime varchar(5) NOT NULL
, Duration int NULL
, FinishTime varchar(5) NULL
, RoomID char(1) NOT NULL
);
GO
INSERT INTO #SourceData ( SlotId, StartTime, Duration, FinishTime, RoomID )
VALUES ( 10909380, '11:00', 60, '12:00', 'A' )
, ( 9553599, '09:00', 120, '11:00', 'C' )
, ( 13204358, '08:30', 120, '10:30', 'D' )
, ( 10886075, '13:00', 240, '17:00', 'J' );
WITH cteData AS (
SELECT s.SlotId, s.RoomID
, StartTime = CAST(DATEADD(hh, DATEDIFF(hh, 0, convert(time(0), s.StartTime , 108)), 0) AS time(0))
, FinishTime = CAST(DATEADD(hh, DATEDIFF(hh, 0, convert(time(0), s.FinishTime, 108)), 0) AS time(0))
FROM #SourceData AS s
)
SELECT cte.SlotId
, StartTime = t.SlotStart
, Duration = DATEDIFF(MINUTE, t.SlotStart, t.SlotEnd)
, FinishTime = t.SlotEnd
, cte.RoomID
FROM cteData AS cte
INNER JOIN dbo.Timeslots AS t
ON cte.StartTime < t.SlotEnd
AND cte.FinishTime > t.SlotStart -
Hi,
Thank you for providing code. It worked well on my dev db.
Regards,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply