Assign new local variable requirement in SQL

Question

I am new to using DECLARE, SET, and EXEC() in SQL so maybe my approach is not the right for this problem so please bear with me. My objective is to reduce the use of duplicate code. I currently have a lot of union statements where the WHERE clause is cumulative. Part of the code looks like this (too long for this post):

EDIT 1: the initial code

-- step 1
select 1 as step, sum(Amount) as totalValue, count(*) as total, 'comment 1' as comment
from table_A
Where 
    requirement1 = 1
    and requirement2 = 100
    and requirement3 = 'A'
-- step 2
union
select 2 as step, sum(Amount) as totalValue, count(*) as total, 'comment 2' as comment
from table_A
Where 
    requirement1 = 1
    and requirement2 = 100
    and requirement3 = 'A'
    and requirement4 = 50
union
.
.
.
-- step n
union
select n as step, sum(Amount) as totalValue, count(*) as total, 'comment n' as comment
from table_A
Where 
    requirement1 = 1
    and requirement2 = 100
    and requirement3 = 'A'
    and requirement4 = 50
    .
    .
    .
    requirementn = 5

EDIT 1: what I am trying to achieve

+------+-------------+-------+------------+
| step | totalAmount | total |  comment   |
+------+-------------+-------+------------+
| 1    |      10000  |    50 | comment 1  |
| 2    |        5000 |   100 | comment 2  |
| 3    |        2000 |   500 | comment 3  |
| .    |           . |     . | .          |
| .    |           . |     . | .          |
| .    |           . |     . | .          |
| n    |        5000 |    10 | comment n  |
+------+-------------+-------+------------+

EDIT 1: how I am trying to solve the problem

I have gotten to the point where I can add the new requirements to the previous one by using a local variable. However, I cannot figure out whether it is possible to do some kind of re-assignment of a variable that has already ben SET. So instead of getting the new name for each step I can only get the step count and comment for step 1. I have added comments where my problems occur. My code looks like this:

    -- general selection requirements
    DECLARE @selectedOverview varchar(1000)
    -- requirements step 1
    DECLARE @stepRequirement1 varchar(1000)
    DECLARE @step varchar(50)
    DECLARE @comment varchar(100)
    DECLARE @requirement1 varchar(1)
    DECLARE @requirement2 varchar(100)
    DECLARE @requirement3 varchar(100)
    SET @step = 1
    SET @comment = 'Total overview'
    SET @requirement1 = 1 
    SET @requirement2 = 100
    SET @requirement3 = 'A'
    -- additional requirements step 2
    DECLARE @stepRequirement2 varchar(1000)
    DECLARE @requirement4 varchar(100)
    SET @requirement4 = 50


    -- dynamic selection
    SET
        @selectedOverview = 
            'select 
                ' + @step + ' as step
                ,sum(totalamount) as beløb
                ,count(*) as antal
                ,' + '''' + @comment + '''' + ' as comment
            from KONVICI.dbo.A_MATRIX 
                where ' 

    -- step2
    SET 
    @stepRequirement1 = 
        @selectedOverview
        +
         'requirement1 = ' + @requirement1
        + 
        ' and requirement2 = ' + @requirement2  
        +
        ' and requirement3 = '  + '''' + @requirement3 + ''''
    EXEC(@stepRequirement3)
    -- step 2
    SET @comment = 'one layer has been removed' -- **PROBLEM: the re-assignment is not working**
    SET @step = @step + 1 -- **PROBLEM: @step is not incremented by 1**
    SET 
    @stepRequirement2 = 
        @stepRequirement1 
        + 
        ' and requirement4 = ' + @requirement4 
    EXEC(@stepRequirement2) .
.
.
-- step n goes here
-- the same approach as for step2

Answer 1

My thought (and pardon me in advance for syntax errors, the idea is pretty clear) is to replace the unions with a case when statement to get the step, will simplify things since all data is from the same table and just used to calculate the step value.

Just make sure you use the right order of expressions as some of your Boolean conditions overlap.

E.g.

select 
    case 
        when requirement1 = 1 and requirement2 = 100 and requirement3 = 'A' and requirement4 = 50 then 2
        when requirement1 = 1 and requirement2 = 100 and requirement3 = 'A' then 1
        ...
        ...
    end as step,
    sum(Amount) as totalValue, count(*) as total, 'comment 1' as comment

Answer 2

You could simplify a bit with a CTE

WITH CTE as (
SELECT sum(Amount) as totalValue, count(*) as total
from table_A
)

SELECT 1, comment = 'comment 1', * FROM CTE 
WHERE     requirement1 = 1
    and requirement2 = 100
    and requirement3 = 'A'

UNION ALL

SELECT 2, comment = 'comment 2', * FROM CTE    
    where requirement1 = 2
    and requirement2 = 100
    and requirement3 = 'A'
    and requirement4 = 50
...

Without dynamic SQL.

Answer 3

A quite simple approach would be to use temporary tables and then add the additional WHERE statements accumulated. This also adds the benefit of being able to see all the observations that fulfill each step criteria. The code could look something like this:

-- drop tables if they exists
drop table if exits #table_step1
drop table if exits #table_step2
...

-- select data from the different steps
-- select data step 1
select *
into #table_step1
from table_A
where
    requirement1 = 1
    and requirement2 = 100
    and requirement3 = 'A'

-- select data step 2
select * 
into #table_step2
from #table_step1
where 
    requirement4 = 50
...

-- aggregate the data for each step and use UNION ALL to get overall overview
select 1 as step, sum(Amount) as totalValue, count(*) as total, 'comment 1' as comment
from #step1

UNION ALL

select 2 as step, sum(Amount) as totalValue, count(*) as total, 'comment 2' as comment

UNION ALL
...