SQLTeam.com | Weblogs | Forums

Split a range of dates into records for every half an hour between begin and end date/hour

#1

Hello
I have in a SQL an agenda table in which i have the user, the date begin and the date end of the appointment.
What i'ld like to do is to split into half an hour every hour between the date begin and the date end.

Example :
User - DateBegin - Date End
1 - 2017-04-27 09:30 - 2017-04-27 11:00

Would return
User - Date Begin - Date End - Time
1 - 2017-04-27 09:30 - 2017-04-27 11:00 - 09:30
1 - 2017-04-27 09:30 - 2017-04-27 11:00 - 10:00
1 - 2017-04-27 09:30 - 2017-04-27 11:00 - 10:30
1 - 2017-04-27 09:30 - 2017-04-27 11:00 - 11:00

Thanks for your help
Cheers
Didier

#2

Try this

declare @starttime datetime
set @starttime = '2017-04-27 09:30'

declare @Endtime datetime
set @Endtime = '2017-04-27 11:30'

;with cte (starttime,endtime,times)
as
(
select @starttime,@Endtime, cast(@starttime as time)[time]
union all
select starttime,endtime, cast(dateadd(MINUTE,30,times) as time)[time] from cte
where times < cast(endtime as time)
)

select * from cte option (maxrecursion 0)

#3

Ho great ...
The trouble with your code which is working :slight_smile: and maybe i wrongly explained is that i have a calendar in which each user is per day entering his various tasks.
So i actually have per user 4 fields, the user name, the name of the task, the datetime begin and the datetime end.
So i'ld need to have per user per task the split in time.
This means that in your code @startime and @endtime are varying on each record and are not constants but variables.
So i'ld need a kind of a loop that would for each record split the record per user per task ...

If you wouldn't mind helping it would save my day :wink:
Thanks a lot in advance

#4

Thanks, and Please look this

CREATE TABLE #Task (
username varchar(50),
taskname varchar(100),
startdate datetime,
Enddate datetime
)
INSERT INTO #Task
VALUES ('Mark', 'Task 1', '2017-04-27 09:30', '2017-04-27 11:30'),
('George', 'Task 1', '2017-04-27 09:30', '2017-04-27 12:30'),
('Mark', 'Task 2', '2017-04-27 12:30', '2017-04-27 15:30')

;
WITH cte (Username, Taskname, starttime, endtime, endtimetemp, times)
AS (SELECT
*,
startdate,
CAST(startdate AS time) [time]
FROM #Task
UNION ALL
SELECT
username,
Taskname,
starttime,
endtime,
DATEADD(MINUTE, 30, endtimetemp),
CAST(DATEADD(MINUTE, 30, times) AS time) [time]
FROM cte
WHERE endtimetemp < endtime)
SELECT
*
FROM cte
ORDER BY 1, 2
OPTION (MAXRECURSION 0)

2 Likes
#5

Thanks a lot, it's WORKING :slight_smile: :slight_smile: :slight_smile:
The only thing is that i have not upgraded SQL Server and i'm still on 2005 so i had to CONVERT(DATEADD(MINUTE,15,times),114) to convert to TIME

Have a nice we
Cheers
Didier

#6

:slight_smile:

#7

Hi
I need your help to create a table with the values returned by the WITH clause into a new table
Could you help please ?
Cheers

#8

If you want create a table like this
;with NewCTE
as
(
select * from TableA
)

select * into NewTable from NewCTE

#9

Hi SQLPS
I should call you SQLMASTER instead ... it works great :slight_smile:
You've made my day :wink:
Thanks thanks thanks