adding trimmed substrings

using sql server:

I have a number of commands that trim a txt file, example:

Case when charindex('-', Substring([RawStreamOut], 179, 10)) > 0   
                then '-' + Replace(LTrim(RTRIM(Substring([RawStreamOut], 179, 9))), ',','')  else
                        LTrim(RTRIM(Substring([RawStreamOut], 179, 10))) end as [Days_Old_16_To_20],

Case when charindex('-', Substring([RawStreamOut], 196, 10)) > 0   
                then '-' + Replace(LTrim(RTRIM(Substring([RawStreamOut], 196, 9))), ',','')  else
                        LTrim(RTRIM(Substring([RawStreamOut], 196, 10))) end as [Days_Old_21_To_40]

etc…

I have a number of these.

current output:

I need to be able to add the results of these commands into a new column named “total”. is there an easy way to do this?

Answer

What you now have is:

select 
    case when charindex........as [Days_Old_16_To_20]
    ,.......
from
    (bunch of tables and joins)

Instead, put your formulas in a cross apply:

select
    q.*
from 
    (bunch of tables and joins)
    cross apply
    ( select
        case when charindex........as [Days_Old_16_To_20]
        ,.......
    ) as q

Now you can add a new column to your select which will be :

select
    q.[Days_Old_16_To_20] + q.[Days_Old_21_To_40] + .... as [Total_value]
    ,q.*
from 
    (bunch of tables and joins)
    cross apply
    ( select
        case when charindex........as [Days_Old_16_To_20]
        ,.......
    ) as q