How do I update a SQL table using a generated variable on each row?

Question

I have a table that contains 4 columns: Project, ScenarioStart, ScenarioEnd and TimePeriods.

TimePeriods is always blank to start with, as this is where I need to populate a string of all applicable Time Periods for a project (inclusive of start and end).

All time periods are formatted yyyyMm

e.g.

Project   ScenarioStart    ScenarioEnd   TimePeriods
1         2024M9           2025M3        
2         2024M6           2024M10

What I now need is to update the table with a text string containing all applicable periods for each row. (I'm using Pipe separation)

So row 1 would be '2024M9|2024M10|2024M11|2024M12|2025M1|2025M2|2025M3'

Row 2 should be '2024M6|2024M7|2024M8|2024M9|2024M10'

I already have a working code to generate the string, but it has 2 problems:

1. It only works with fixed start and end values, rather than reading each data row and using the start and end from the existing columns.
2. It is currently just producing the string as a variable, not updating the table with the value.

The code to generate the string is below. Can someone please help me to convert it to an update for every row in the table please?

-- Create the variables to hold Start- and End-Period, and the blank Period List --
-- Currently only works if hard-coded, but should read the table cells)

declare @sScenarioStart nvarchar(10) = '2024M1',
        @sScenarioEnd nvarchar(10) = '2026M3',
        @SplitPeriods nvarchar(max) = ''

----------------------------------------------------------------
-- Create a list of valid time references for this submission --
----------------------------------------------------------------
Declare @sCounterTime nvarchar(10) = @sScenarioStart,
        @sCounterYear nvarchar(4) = substring(@sScenarioStart,1,4),
        @sCounterMonth nvarchar(4) = substring(@sScenarioStart,6,len(@sScenarioStart)-5)

        print 'Counter Time: ' + @sCounterTime + 'Counter Year: ' + @sCounterYear + 'Counter Month: ' + @sCounterMonth

-- Generate the comma-sparated list of time periods --
While @sCounterTime <> @sScenarioEnd
    BEGIN
        -- Add the initial Time Period to the output string
        set @SplitPeriods = @SplitPeriods + @sCounterTime + '|'

        -- If Current month is December then +1 the year and reset the month back to 1
        set @sCounterYear =     case @sCounterMonth
                                    when 12 then @sCounterYear + 1
                                    else @sCounterYear
                                end
        
        set @sCounterMonth =    case @sCounterMonth
                                    when 12 then 1
                                    else @sCounterMonth + 1
                                end

        -- Move the Time Period forward
        set @sCounterTime = @sCounterYear + 'M' + @sCounterMonth

    END     -- End of BEGIN statement

-- Add the final Time Period to the string
set @SplitPeriods = @SplitPeriods + @sCounterTime

-- TESTING: Output the final string
print 'Final Period String: ' + @SplitPeriods
            

The output from this script is:

Counter Time: 2024M1Counter Year: 2024Counter Month: 1

Final Period String: 2024M1|2024M2|2024M3|2024M4|2024M5|2024M6|2024M7|2024M8|2024M9|2024M10|2024M11|2024M12|2025M1|2025M2|2025M3|2025M4|2025M5|2025M6|2025M7|2025M8|2025M9|2025M10|2025M11|2025M12|2026M1|2026M2|2026M3

Both of these outputs are correct, but the second one needs to be updated in the TimePeriods column for each row.

Thanks

Answer 1

I put your records into an Example table. I then converted ScenarioStart and ScenarioEnd to Dates. I was then was able to use a recursive CTE to generate the MonthDates. I combined the MonthDates using String_Agg. I then updated the TimePeriods field in the example table.

CREATE TABLE Example 
(
    Project VARCHAR(512),
    ScenarioStart   VARCHAR(512),
    ScenarioEnd VARCHAR(512),
    TimePeriods VARCHAR(512)
);

INSERT INTO Example (Project, ScenarioStart, ScenarioEnd) VALUES
    ('1', '2024M9', '2025M3'),
    ('2', '2024M6', '2024M10');

SELECT * FROM Example;

-- Recursive CTE to generate a list of months within the date range:
WITH Months AS (
    SELECT Example.Project, CONVERT(DATE, DATEADD(D, -(DAY(CONVERT(Date,  Substring(ScenarioStart,1,4) + '-' + Substring(ScenarioStart,6,2) + '-1'))) + 1, CONVERT(Date,  Substring(ScenarioStart,1,4) + '-' + Substring(ScenarioStart,6,2) + '-1'))) [MonthDate]
    FROM Example
    UNION ALL
    SELECT Example.Project, DATEADD(M, 1, MonthDate)
    FROM Months   
    INNER JOIN Example on Example.Project=Months.Project
    WHERE MonthDate <= DATEADD(M, -1, CONVERT(Date,Substring(Example.ScenarioEnd,1,4) + '-' + Substring(Example.ScenarioEnd,6,2) + '-1'))
)
UPDATE Example
  SET Example.TimePeriods=t.TimePeriods
FROM Example E
INNER JOIN
(
  --Combine MonthDates using STRING_AGG to make TimePeriods 
  SELECT Project, STRING_AGG(CAST(Year(MONTHDATE)  as Varchar(4))+'M'+Cast(Month(Monthdate) as varchar(2)), '|') as TimePeriods 
  FROM Months
  GROUP BY Project 
) t
ON E.Project=t.Project

SELECT * FROM Example;

fiddle

Project	ScenarioStart	ScenarioEnd	TimePeriods
1	2024M9	2025M3	2024M9│2024M10│2024M11│2024M12│2025M1│2025M2│2025M3
2	2024M6	2024M10	2024M6│2024M7│2024M8│2024M9│2024M10