Is is possible to combine local variables in a case statement? DECLARE @CalcQuery varchar(340)
SET @CalcQuery = (SELECT
    sum(T0.[LineTotal])
FROM 
    RDR1 T0  
        INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
        INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
        INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]
WHERE
    T3.[Name] = 'Wild Heart SP-1' 
    AND 
    T0.[Project] = 'ATL Market Molina')

SELECT DISTINCT
T0.[Project],
CASE 
   WHEN T0.[Project] = 'ATL Market Molina' 
   THEN @CalcQuery 
   END [Season Total]
FROM
RDR1 T0  
INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]


Is there a way for me to Nest 50 Project codes and 7 Seasons without writing redundant SQL and Case statements. I think that the only way to go would be to declare variables for the project codes and seasons. The only issue is that I'm unsure on how to merge them with @CalcQuery. I wrote out the SQL without the variables and it's long.

The output would be the total for the specific season 'Wild Heart' and project 'ATL Market Molina'. The company that requested this didn't plan ahead and created many order tags. T3.[Name] = 'Wild Heart SP-1' is the season name.
Is there any reason you aren't just using a group by?

SELECT T0.[Project], T3.[Name] AS [Season],
     sum(T0.[LineTotal]) AS [Season Total]
FROM RDR1 T0  
INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]
GROUP BY T0.[Project], T3.[Season];