Optimize a Repeating Union All SSMS Query

Question

I'm creating a report that will get uploaded to QuickBooks. I have to UNION ALL 7 queries due to a single calculation change in each one. Below is an example of the first two queries:

SELECT
    Clients.Id
    ,@CNumber as 'Check Number'
    ,@TDate as 'Transac Date'
    ,CONCAT(@Memo, 'Debt') as 'Memo'
    ,Compensation.Payment as 'Amount'

FROM Compensation

    JOIN Clients on Id = Compensation.ClientId

UNION ALL

SELECT
    Clients.Id
    ,@CNumber as 'Check Number'
    ,@TDate as 'Transac Date'
    ,CONCAT(@Memo, 'Insurance') as 'Memo'
    ,InsuranceInvoices.WeeklyAmount

FROM InsuranceInvoices

Is there any way to eliminate the need to repeat the Clients.Id, @CNumber, @TDate, CONCAT(@Memo, Variable) for every single union?

For the CONCAT(@MEMO, variable) as 'Memo", I can perhaps create an array and insert on each new iteration of Union ALL (don't know if that's even possible).

Where I'm stuck is, not sure how to create a loop which iterates the first 4 columns, on each new calculation of 'Amount'.

This is solely for the purposes of optimization/code readability. The query works and it's not all that slow. However, it's 155 lines long and I wonder if the next time I do something similar I can try doing so differently.

What I've researched so far: The following Stack Overflow link is somewhat similar, however, it's not using different 'FROM' Tables. The data is coming from a single table. My data is coming from 4 tables. Below a sample of two queries from the same table:

    SELECT
        Clients.Id
        ,@CNumber as 'Check Number'
        ,@TDate as 'Transac Date'
        ,CONCAT(@Memo, 'Debt') as 'Memo'
        ,Compensation.PaybackAmount as 'Amount'

    FROM Compensation

        JOIN Clients on Id = Compensation.ClientId

    WHERE
        Compensation.Week = @TrailingWeekEnd

UNION ALL

    SELECT
        Clients.Id
        ,@CNumber as 'Check Number'
        ,@TDate as 'Transac Date'
        ,CONCAT(@Memo, 'Vehicle') as 'Memo'
        ,Compensation.VDAccumulation as 'Amount'

    FROM ContractorSettlements

        JOIN Clients on Id = Compensation.ClientId

    WHERE
        Compensation.Week = @TrailingWeekEnd

If this is a bad question, my apologies. In my mind, I imagine this working within a loop but I honestly don't know how to optimize it. If there is any more info I can provide, please let me know.

Answer 1

Despite of the wrong syntax you have in the query but you can use the following

Explanation

1. Query only the data required to extract from the tables
2. Differentiate the data by adding a type for each table like Debt and Vehicle
3. Combine the result with the parameters you want

Query

;with report as(
    select [Id] = ClientId,
           [Amount] = PaybackAmount,
           [Type] = 'Debt'
    Compensation c 
    where c.Week = @TrailingWeekEnd
    union all
    select ClientId,
           VDAccumulation,
           'Vehicle'
    from ContractorSettlements c
    where c.Week = @TrailingWeekEnd
)
select Id,
       [Check Number] = @CNumber,
       [Transac Date] = @TDate,
       [Memo] = Concat(@Memo,[Type]),
       Amount
from report

Hope this will help you