Filtering Results of a CTE to eventually end up in a SQL report

Question

I am working with SSMS, SQL Server, and SSRS. I have results in a CTE that I want to filter further 'after' it is run.

The query looks for multiple entries of ID's in a table. Then it selects the ones that have null values. This is a correct selection and I need to narrow the scope down further.

At this point I would in the past dump the output of this into an excel file and look for and highlight duplicate values in the ID field and only show those rows. My goal is to get this into a SSRS report.

Do I just need to create a view then run another CTE view against it, some type of nested query, or store a procedure and filter out the results or use a user defined function? Can I use something like a comma after the first CTE and maybe do another WITH statement? Thanks in advance.

WITH T AS
(
SELECT [ID] ,
    [LOCATION] ,
    [LOCATION_START_DATE],
    [LOCATION_END_DATE],
    [POS],
    COUNT(*) OVER (PARTITION BY [ID]) as Cnt
FROM LOCATIONS_TABLE WITH (NOLOCK)
)
SELECT  [ID] ,
    [LOCATION] ,
    [LOCATION_START_DATE],
    [LOCATION_END_DATE],
    [POS]
FROM T
WHERE Cnt >= 2
AND LOCATION_END_DATE IS NULL
ORDER BY ID

Answer 1

You can cascade several CTEs as follows:

WITH 
    T AS (
        SELECT [ID] ,
            [LOCATION] ,
            [LOCATION_START_DATE],
            [LOCATION_END_DATE],
            [POS],
            COUNT(*) OVER (PARTITION BY [ID]) as Cnt
        FROM LOCATIONS_TABLE WITH (NOLOCK)
    ),
    U AS (
        SELECT  [ID] ,
            [LOCATION] ,
            [LOCATION_START_DATE],
            [LOCATION_END_DATE],
            [POS]
        FROM T
        WHERE Cnt >= 2
        AND LOCATION_END_DATE IS NULL
    )
SELECT ...