Combine values from columns whose name matches a variable

Question

I have a table with the following names, based on months:

2024M1	2024M2	2024M3	2024M4	YTD
100	125	150	175	(NULL)
110	120	130	140	(NULL)

If I set a variable for one of the months, I need to sum the values of all columns, where the column name <= the variable, into the YTD column.

So if the variable is set to '2024M3', then row 1 would equal 375, and row 2 would be 360.

What is the simplest/most effective way to do this please? (Avoiding dynamic SQL if at all possible).

Answer 1

Here's an option that my help.

Example

Declare @YourTable Table ([2024M1] int,[2024M2] int,[2024M3] int,[2024M4] int,[YTD] varchar(50))  Insert Into @YourTable Values 
 (100,125,150,175,null)
,(110,120,130,140,null)

Declare @Year varchar(4)='2024'
Declare @Mnth int=3
 
;with cte as (
Select * 
 from @YourTable A
  Cross Apply ( Select _YTD = sum(try_convert(money,value))
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where left([key],4) = @Year
                  and try_convert(int,replace(right([key],2),'M',''))<=@Mnth
             ) B
)
Update cte set YTD=_YTD

Select * from @YourTable

Results

2024M1  2024M2  2024M3  2024M4  YTD
100     125     150     175     375.00
110     120     130     140     360.00

If you want an update dbFiddle