Is there a better way to SUM columns?

Question

I have the answer that I need, but I am asking is there is a better method of getting the same answer. "4" will be a parameter ThisMonth. I am trying to get the budget numbers YTD. The table does not have a date column, hence this strange way of getting a SUM based on period.

SELECT СASE 
         WHEN 4 <= 1 THEN SUM(Period.A)
         WHEN 4 <= 2 THEN SUM(Period.A) + SUM(Period.B)
         WHEN 4 <= 3 THEN SUM(Period.A) + SUM(Period.B) + SUM(Period.C)
         WHEN 4 <= 4 THEN SUM(Period.A) + SUM(Period.B) + SUM(Period.C) + SUM(Period.D)
         WHEN 4 <= 5 THEN SUM(Period.A) + SUM(Period.B) + SUM(Period.C) + SUM(Period.D) 
                        + SUM(Period.E)
         WHEN 4 <= 6 THEN SUM(Period.A) + SUM(Period.B) + SUM(Period.C) + SUM(Period.D) 
                        + SUM(Period.E) + SUM(Period.F)
         WHEN 4 <= 7 THEN SUM(Period.A) + SUM(Period.B) + SUM(Period.C) + SUM(Period.D) 
                        + SUM(Period.E) + SUM(Period.F) + SUM(Period.G)
         WHEN 4 <= 8 THEN SUM(Period.A) + SUM(Period.B) + SUM(Period.C) + SUM(Period.D) 
                        + SUM(Period.E) + SUM(Period.F) + SUM(Period.G) + SUM(Period.H)
      ELSE 0 
      END AS [LaborCOGS-CO],
   '0' AS [LaborCOGS-AO], '0' AS IndirectLabor,
   '0' AS MiscExpense,    '0' AS LabelCost,
   '0' AS BuildMaint,     '0' AS ShipCost,
   '0' AS RDR,            '0' AS NetSales
From GLAccountBudgetDetails
LEFT JOIN GLAccountBudget WITH(NOLOCK) 
    ON GLAccountBudgetDetails.GLAccountBudgetID = GLAccountBudget.GLAccountBudgetID
LEFT JOIN
( SELECT GLAccountBudgetDetailID,[BudgetAmtPeriod1] A,[BudgetAmtPeriod2] B,
     [BudgetAmtPeriod3] C,[BudgetAmtPeriod4] D,[BudgetAmtPeriod5] E,
     [BudgetAmtPeriod6] F,[BudgetAmtPeriod7] G,[BudgetAmtPeriod8] H,
     [BudgetAmtPeriod9] I,[BudgetAmtPeriod10] J,[BudgetAmtPeriod11] K,
     [BudgetAmtPeriod12] L
  From GLAccountBudgetDetails
) AS Period 
   ON GLAccountBudgetDetails.GLAccountBudgetDetailID = [Period].GLAccountBudgetDetailID
WHERE GLAccountBudgetDetails.GLAccountID IN(256,257,258,266) 
   AND GLAccountBudget.FiscalYear = 2024

This is the GLAccountBudgetDetails table. The last four columns are all NULL, so that is not helpful.

COLUMN_NAME DATA_TYPE
GLAccountBudgetDetailID int
GLAccountBudgetID   int
GLAccountID int
BudgetAmtPeriod1    float
BudgetAmtPeriod2    float
BudgetAmtPeriod3    float
BudgetAmtPeriod4    float
BudgetAmtPeriod5    float
BudgetAmtPeriod6    float
BudgetAmtPeriod7    float
BudgetAmtPeriod8    float
BudgetAmtPeriod9    float
BudgetAmtPeriod10   float
BudgetAmtPeriod11   float
BudgetAmtPeriod12   float
BudgetAmtPeriod13   float
BudgetAmtPeriod14   float
BudgetAmtPeriod15   float
UserCreated varchar
DateCreated datetime
UserModified    varchar
DateModified    datetime

Here is the GLAccountBudget table.

COLUMN_NAME DATA_TYPE
GLAccountBudgetID   int
Name    varchar
FiscalYear  int
DefaultBudget   bit
UserCreated varchar
DateCreated datetime
UserModified    varchar
DateModified    datetime

Answer 1

Bad schemas mean bad queries. We can create a view to pretend it's a good schema with one row per period. Then query that view. At least then you only need to do it once.

Here's a simplified version of your problem.

create table bad_schema (
  some_id integer not null,
  month1 integer,
  month2 integer,
  month3 integer,
  month4 integer,
  month5 integer,
  month6 integer,
  month7 integer,
  month8 integer,
  month9 integer,
  month10 integer,
  month11 integer,
  month12 integer,
  year integer not null,
  primary key(some_id, year)
);

We can create a view to turn it into some_id, a date, and the data.

create view good_schema as (
  select 
    some_id,
    cast(concat(year, '-', months.value, '-', '01') as date) as this_month,
    case months.value
    when 1 then month1
    when 2 then month2
    when 3 then month3
    when 4 then month4
    when 5 then month5
    when 6 then month6
    when 7 then month7
    when 8 then month8
    when 9 then month9
    when 10 then month10
    when 11 then month11
    when 12 then month12
    else 'what'
    end as data
  from bad_schema
  cross join (
    select * from generate_series(1, 12)
  ) as months
)

Then we can query that in the usual ways.

select * from good_schema

some_id     this_month  data
1           2023-01-01  1
1           2023-02-01  2
1           2023-03-01  3
1           2023-04-01  4
1           2023-05-01  5
1           2023-06-01  6
1           2023-07-01  7
...etc...

select sum(data) 
from good_schema
where this_month between '2023-01-01' and '2023-05-01'
group by some_id

Demonstration.

You can invert this technique and alter the table to be sensible and leave a view behind to support legacy queries.