YTD budget table to current period in SQL

Question

I have a table in the following format (pseudo code):

CREATE TABLE [GLM_MASTER__BUDGET](
    [Budget_Year],
    [Budget_Type],
    [Account],
    [Period_1_Budget],
    [Period_2_Budget],
    [Period_3_Budget],
    [Period_4_Budget],
    [Period_5_Budget],
    [Period_6_Budget],
    [Period_7_Budget],
    [Period_8_Budget],
    [Period_9_Budget],
    [Period_10_Budget],
    [Period_11_Budget],
    [Period_12_Budget]
    )

I need to be able to query this with a with of a int for the current period like 6

This should return the the combined values of all periods from 1 to the number specified.

So I would end up with an output like this:

Budget_Year, Budget Type, Account, (Sum of all periods from 1 to the period specified) as Amount

I am using MS SQL server so it would be ok at the top to do something like:

DECLARE @current_period INT = 6 

Answer 1

If you have the opportunity to redesign this table you may wish to consider it now.

You could use something along these lines:

DECLARE @current_period INT = 6 



SELECT 
Budget_Year, Budget Type, Account,
CASE WHEN @current_period >= 1 THEN [Period_1_Budget] ELSE 0 END +
CASE WHEN @current_period >= 2 THEN [Period_2_Budget] ELSE 0 END +
CASE WHEN @current_period >= 3 THEN [Period_3_Budget] ELSE 0 END +
CASE WHEN @current_period >= 4 THEN [Period_4_Budget] ELSE 0 END +
.....
..
CASE WHEN @current_period >= 12 THEN [Period_12_Budget] ELSE 0 END As Amount
FROM [GLM_MASTER__BUDGET]

Also it's not clear from your description whether you require a SUM and GROUP BY so I've left it out.