Filtering on window functions in SQL Server

Question

I generated a CTE called mycte from 5 select statements using union to combine them. The output looks like this for a particular job:

ID	JOB_ID	STATUS	BASE_ID	PERCENTAGE
20DA	GBR01	0	12	20
21DA	GBR01	0	12	30
21DA	GBR01	0	14	50

For every unique JOB_ID the sum of the percentage must be 100%.

To test my CTE, I used:

SELECT JOB_ID, SUM(PERCENTAGE) AS myTOTAL
FROM myCTE
GROUP BY JOB_ID
HAVING SUM(PERCENTAGE) <> 100
ORDER BY SUM(PERCENTAGE)

The output showed that not all sum up to 100 because of dirty data in the database. I then attempted to extract 2 different tables, one for PERCENTAGE = 100% and the other for <> 100%.

Since the columns I needed to extract for the new table are ID, JOB_ID, STATUS, BASE_ID and PERCENTAGE, I then applied

SELECT
    ID, JOB_ID, STATUS, BASE_ID, PERCENTAGE,
    SUM(percentage) OVER (PARTITION BY JOB_ID, BASE_ID, ID) AS PERCENTAGE_SUM
FROM
    mycte

Unfortunately where clause will not work on window function.

Question: how do I extract only ID, JOB_ID, STATUS, BASE_ID, PERCENTAGE from mycte where sum of the percentage = 100?

Answer 1

Looking at the sample data it looks like you need to partition by JOB_ID only:

WITH mycte AS (
    ...
), cte2 as (
    SELECT
        ID, JOB_ID, STATUS, BASE_ID, PERCENTAGE,
        SUM(percentage) OVER (PARTITION BY JOB_ID) AS PERCENTAGE_SUM 
    FROM mycte
)
SELECT *
FROM cte2
WHERE PERCENTAGE_SUM = 100