This is EXACTLY what I needed. This was far too complex for me to comprehend, so I started to look into why it works and breaking down everything in piece. No comments in anything for the query below, but should be able to figure it out for someone looking at the same thing:
SELECT
DATEDIFF(HOUR, '1:00', '23:00')
SELECT
DATEDIFF(HOUR, '1:00', '23:00') / 4
SELECT
(1 + DATEDIFF(HOUR, '1:00', '23:00')) / 4
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM
sys.all_columns
SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As N
FROM
sys.all_columns
SELECT
DATEADD(HOUR, 5 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 4 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 3 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 2 * 4, '1:00') As ScheduleTime
UNION
SELECT
DATEADD(HOUR, 1 * 4, '1:00') As ScheduleTime
SELECT
',' + CAST(T.ScheduleTime As char(5))
FROM
/* Tally table: */
(
SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
FROM
sys.all_columns
) As N
/* Generated schedule: */
CROSS APPLY
(
SELECT DateAdd(hour, N.n * 4, '1:00') As ScheduleTime
) As T
FOR XML PATH('')
I had to make an adjustment to the final query though to make sure I was getting a schedule for a 24 hour period instead from the start time to midnight:
WITH cteSource As
(
SELECT CAST('13:00' As time) As StartTime, 4 As Hours
UNION SELECT CAST('01:00' As time) As StartTime, 7 As Hours
)
SELECT
StartTime,
Hours
,STUFF(T.ScheduleTime, 1, 1, '') As ScheduleTime
FROM
cteSource As S
CROSS APPLY
(
SELECT
',' + CAST(T.ScheduleTime As char(5))
FROM
/\* Tally table: \*/
(
SELECT TOP (24 / S.Hours)
ROW\_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
FROM
sys.all\_columns
) As N
/\* Generated schedule: \*/
CROSS APPLY
(
SELECT DateAdd(hour, N.n \* S.Hours, S.StartTime) As ScheduleTime
) As T
FOR XML PATH('')
) As T (ScheduleTime)
