There are two issues at play here:
- the code is redundant because the window function is typed out twice
- the concern is that SQL Server will perform the count twice since it is referenced twice
For the first, we can address the tidiness aspect by only calculating that expression once (and I would change from
NOT IN (NULL, 0) logic to just checking if the count is greater than 0 (which eliminates NULLs and 0):
;WITH x AS
SELECT x, y, z, L_Count,
q = COUNT (PH.header_id) OVER (PARTITION BY PL.header_id)
FROM ...something PH and PL...
SELECT x, y, z, quantity = CASE WHEN q > 0 THEN L_Count ELSE q END
Whether that's the right or most efficient partitioning choice is a different exercise.
For the second, well, SQL Server is pretty smart about performing a calculation only once at a given scope. As mentioned above, referencing an expression twice doesn't necessarily mean it will be calculated twice, and hiding a second reference inside a CTE like I do in this answer doesn't guarantee that it will be calculated exactly once, either. To know for sure, you'd have to examine the execution plans and other details (like the expressions tab and performance metrics provided by, say, Plan Explorer), and compare.
Having table structure, sample data, and desired results (and even more of the query) would really help create a better (and validated) solution for you. I'm only suggesting the same logic you have in your question, which I can only assume is working for you right now (I'm trying to understand what your
CASE logic even means - it seems to say "if there is a valid count from this window function, then use this other column, but if there isn't a valid count from this window function, use the invalid count"). I suspect something got lost in translation / simplification.