Interval schedule

Member 14623989

I'm tryin to figure out how to get a schedule based on a start time and the hourly interval in TSQL. I've been searching the whole day and I just can't find an idea on how to do it.

SELECT
'01:00' AS StartTime
,4 AS Hours

What I need is a column that has the the times listed for the whole day:

StartTime Hours Schedule
01:00 4 01:00,05:00,09:00,13:00,17:00,21:00

Victor Nijegorodov

Could it help? [How can I schedule a daily backup with SQL Server Express? - Stack Overflow](https://stackoverflow.com/questions/487675/how-can-i-schedule-a-daily-backup-with-sql-server-express)

Richard Deeming

You'll need a tally table and a way to concatenate multiple string values. If you're using SQL Server 2017 or later, you can use STRING_AGG[^]; otherwise, use an alternative method[^]. For example:

SELECT
StartTime,
Hours,
STUFF(T.ScheduleTime, 1, 1, '') As ScheduleTime
FROM
YourSourceTable As S
CROSS APPLY
(
SELECT
',' + CAST(T.ScheduleTime As char(5))
FROM
/* Tally table: */
(
SELECT TOP ((1 + DateDiff(hour, S.StartTime, '23:00')) / 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)
;

Demo[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Member 14623989

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)