simplify SQL statement by using CTE

Question

I have a query like the following:

SELECT A.a, A.b, B.c,
(CASE WHEN ... THEN ... ELSE ... END) AS CalculatedValue,
B.d
FROM    dbo.TableA A INNER JOIN
        dbo.TableB B ON (...)
WHERE (CASE WHEN ... THEN ... ELSE ... END) BETWEEN @DayStart AND @DayEnd
GROUP BY A.a, (CASE WHEN ... THEN ... ELSE ... END), B.c

to avoid repeating many times the exact same expression: (CASE WHEN ... THEN ... ELSE ... END) I wanted to define a CTE and query such table using in the select, where and group by the expression CalculatedValue

unfortunately this does not work because the select needs to already include the group by when creating the CTE

is there any other way I could use to not repeat the CASE WHEN... so many times?

Flammable

Answer 1

Use CROSS APPLY, which can be used to define aliased fields and then refer to them:

SELECT A.a, 
       A.b, 
       B.c,
       CalculatedValue,
       B.d
FROM    
       dbo.TableA A 
INNER JOIN
        dbo.TableB B 
        ON (...)
CROSS APPLY 
        (SELECT (CASE WHEN ... THEN ... ELSE ... END)) CxA(CalculatedValue)
WHERE CalculatedValue BETWEEN @DayStart AND @DayEnd
GROUP BY A.a, CalculatedValue, B.c

The CxA is just an alias and you can name it whatever you like.

Glaive

Thanks JNK, this solved the issue, same execution time but way cleaner code than before, +1 and green flag ;-) – Flammable

Happy to help! I only recently learned of this myself and it's made a lot of things simpler. – Glaive

It gets really fun when encapsulating common logic in table valued functions. Queries can end up reading table CROSS APPLY udf1 CROSS APPLY udf2 CROSS APPLY udf3 and all the 'normal' SQL hidden away! – Benedict

actually checking performances again I see that with repetition of code but without the CROSS APPLY my original query takes ~22 seconds and with nicer and neater syntax and CROSS APPLY takes ~1:22 seconds, an overhead of 1 minute for having nicer syntax :( I can't use it like this... – Flammable

@DavidePiras - It should still only calculate once. If it's a performance issue, open a new Q and we can likely help if we knew table structures and details of the query. I can't think of a reason off the top of my head that putting this into a CROSS APPLY would cause a perf issue. – Glaive

Answer 2

For above, I think you could just do two layers of CTE's. The first would do Calculate for all, the second would select from first CTE and filter based on the calculated value. The final query would join in the second layer CTE.

Just a thought.

Berar