asked
SQL Time Interval
Hi guys, I have a table with start time and end time of individual activities and would like a way to split the data into 30 minutes interval. **What I have** Activity Break Start Time 9:00 End Time 10:30 **What I want** Activity Start Time End Time Break 9:30 10:00 Break 10:00 10:30
How about something like this? I'm a big fan of having a date and time utility table to use for cases like this. CREATE TABLE #Activities (Activity varchar(25),StartTime time,EndTime time) INSERT INTO #Activities (Activity,StartTime,EndTime) VALUES ('Break', '09:00', '10:30' ) ,('Break', '11:00', '12:00' ) ,('Break', '14:00', '18:30' ) ,('Break', '05:00', '05:30' ) CREATE TABLE #times ( TimeInterval time ) DECLARE @Time time = TIMEFROMPARTS(0,0,0,0,0) INSERT INTO #times SELECT @Time SET @Time = DATEADD(MINUTE,30,@Time) WHILE @Time > '00:00' BEGIN INSERT INTO #times SELECT @Time SET @Time = DATEADD(MINUTE,30,@Time) END GO SELECT a.Activity,a.StartTime,a.EndTime,t.TimeInterval FROM #Activities a CROSS JOIN #times t WHERE t.TimeInterval >= a.StartTime AND t.TimeInterval < a.EndTime
how about something like this? I am a big fan of date and time tables for cases like this. `CREATE TABLE #Activities (Activity varchar(25),StartTime time,EndTime time) INSERT INTO #Activities (Activity,StartTime,EndTime) VALUES (''Break'', ''09:00'', ''10:30'' ) ,(''Break'', ''11:00'', ''12:00'' ) ,(''Break'', ''14:00'', ''18:30'' ) ,(''Break'', ''05:00'', ''05:30'' ) CREATE TABLE #times ( TimeInterval time ) DECLARE @Time time = TIMEFROMPARTS(0,0,0,0,0) INSERT INTO #times SELECT @Time SET @Time = DATEADD(MINUTE,30,@Time) WHILE @Time > ''00:00'' BEGIN INSERT INTO #times SELECT @Time SET @Time = DATEADD(MINUTE,30,@Time) END GO SELECT a.Activity,a.StartTime,a.EndTime,t.TimeInterval FROM #Activities a CROSS JOIN #times t WHERE t.TimeInterval >= a.StartTime AND t.TimeInterval < a.EndTime `
18 People are following this question.
