'Filtering on window functions in SQL Server [duplicate]

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source