How do I create a loop that generates future dates based on different frequencies and intervals until a specified end date?

Question

I have a data set that appears like this:

next_generation_date	procedure	interval	frequency
2021-01-17 00:00:00.000	Clean Restroom	1	day
2021-01-17 00:00:00.000	Vacuum	2	week
2021-02-01 00:00:00.000	Inspect Fire Extinguisher	3	month
2021-10-01 00:00:00.000	Test Generator	1	year

My goal is to generate multiple date rows for each procedure by going off of the next_generation_date, interval and frequency columns until a specified end date. This specified end date would be the same date throughout the entire table. In this example, let's make the specified end date 2023-12-31. My end goal is for the table to appear similar to the below. Please note since this is an example, I didn't include every row as to avoid having hundreds of rows listed below.

next_generation_date	procedure	interval	frequency
2021-01-17 00:00:00.000	Clean Restroom	1	day
2021-01-18 00:00:00.000	Clean Restroom	1	day
2021-01-19 00:00:00.000	Clean Restroom	1	day
2021-01-20 00:00:00.000	Clean Restroom	1	day
2021-01-17 00:00:00.000	Vacuum	2	week
2021-01-31 00:00:00.000	Vacuum	2	week
2021-02-14 00:00:00.000	Vacuum	2	week
2021-02-28 00:00:00.000	Vacuum	2	week
2021-02-01 00:00:00.000	Inspect Fire Extinguisher	3	month
2021-05-01 00:00:00.000	Inspect Fire Extinguisher	3	month
2021-08-01 00:00:00.000	Inspect Fire Extinguisher	3	month
2021-11-01 00:00:00.000	Inspect Fire Extinguisher	3	month
2021-10-01 00:00:00.000	Test Generator	1	year
2022-10-01 00:00:00.000	Test Generator	1	year
2023-10-01 00:00:00.000	Test Generator	1	year

Below is my most recent attempt in Microsoft SQL Server Management Studio but when I execute the code, I'm presented with the following message: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression".

DECLARE @EndDate AS DATE = '2023-12-31'

SELECT [procedure], [frequency], [interval], [next_generation_date] FROM [data].[table]

WHILE (@EndDate > (SELECT [next_generation_date] FROM [data].[table]))

IF (SELECT [frequency] FROM [data].[table]) = 'day'
       BEGIN
             SELECT DATEADD(day, (SELECT [interval] FROM [data].[table]), (SELECT [next_generation_date] FROM [data].[table]))
       END
ELSE IF (SELECT [frequency] FROM [data].[table]) = 'week'
       BEGIN
             SELECT DATEADD(week, (SELECT [interval] FROM [data].[table]), (SELECT [next_generation_date] FROM [data].[table]))
       END
ELSE IF (SELECT [frequency] FROM [data].[table]) = 'month'
       BEGIN
             SELECT DATEADD(month, (SELECT [interval] FROM [data].[table]), (SELECT [next_generation_date] FROM [data].[table]))
       END
ELSE IF (SELECT [frequency] FROM [data].[table]) = 'year'
       BEGIN
             SELECT DATEADD(year, (SELECT [interval] FROM [data].[table]), (SELECT [next_generation_date] FROM [data].[table]))
       END

Any and all suggestions are greatly appreciated as I'm new to SQL. Thank you.

Answer 1

I would create a helper table that contains many integers, first.

CREATE TABLE many_integers (idx INT);
WITH units(units) AS (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
)
,tens             AS(SELECT units *        10 AS tens             FROM units       )
,hundreds         AS(SELECT tens  *        10 AS hundreds         FROM tens        )
,thousands        AS(SELECT hundreds *     10 AS thousands        FROM hundreds    )
,tenthousands     AS(SELECT thousands *    10 AS tenthousands     FROM thousands   )
,hundredthousands AS(SELECT tenthousands * 10 AS hundredthousands FROM tenthousands)                                                                                                                                        
INSERT                                                                                                                                                                                                                      
INTO many_integers
SELECT  hundredthousands +tenthousands +thousands +hundreds +tens +units 
FROM       units 
CROSS JOIN tens 
CROSS JOIN hundreds 
CROSS JOIN thousands 
CROSS JOIN tenthousands 
CROSS JOIN hundredthousands ;

Then cross join my in-data table with that many_integers table:

-- your input data, don't use in real query ...
WITH
indata(next_generation_date,proced,interv,frequency) AS (
          SELECT  '2021-01-17 00:00:00.000','Clean Restroom'           ,1,'day'
UNION ALL SELECT  '2021-01-17 00:00:00.000','Vacuum'                   ,2,'week'
UNION ALL SELECT  '2021-02-01 00:00:00.000','Inspect Fire Extinguisher',3,'month'
UNION ALL SELECT  '2021-10-01 00:00:00.000','Test Generator'           ,1,'year'
)
-- end of your input data - real query starts here ...
SELECT 
  CASE frequency
    WHEN 'day'   THEN  DATEADD( day   , idx , next_generation_date)
    WHEN 'week'  THEN  DATEADD( week  , idx , next_generation_date)
    WHEN 'month' THEN  DATEADD( month , idx , next_generation_date)
    WHEN 'year'  THEN  DATEADD( year  , idx , next_generation_date)
  END AS next_generation_date
, proced
, interv
, frequency
FROM indata CROSS JOIN many_integers
WHERE idx < 5000
 AND
  CASE frequency
    WHEN 'day'   THEN  DATEADD( day   , idx , next_generation_date)
    WHEN 'week'  THEN  DATEADD( week  , idx , next_generation_date)
    WHEN 'month' THEN  DATEADD( month , idx , next_generation_date)
    WHEN 'year'  THEN  DATEADD( year  , idx , next_generation_date)
  END <=  '2023-12-31 00:00:00'
ORDER BY 1
;                  ```