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
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)
)
Ho great ...
The trouble with your code which is working 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
Thanks a lot in advance
;
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)
Thanks a lot, it's WORKING
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