SQL Except Operator and with CTE to select all the items in 1st query but not in 2nd Query

Question

I am trying to get all the data in the 1st query below but except the data from the 2nd query below.

Here 1st I am trying to select the unique data/distinct data by using with cte and partition by.

I tried using except, but I get this error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.***

First query:

    With cte as
    (
        select 
            *, 
            row_number() over (partition by [Employee ID] order by [QTR] DESC, Wk desc) rownumber 
        from 
            tbl_HC
    )
    select * 
    from cte 
    where rownumber = 1
      and QTR = (Select max(QTR) from tbl_HC)

    Except

    --2nd query
    With cte as
    (
         select 
             *, 
             row_number() over (partition by [Employee ID] order by [QTR] DESC, Wk desc) rownumber 
         from  
             tbl_HC
    )
    select * 
    from cte 
    where rownumber = 1
        and Wk= (
        Select max(Wk) from tbl_HC
        where QTR = (Select max(QTR) from tbl_HC))`

A `WITH` goes at the start of you statement, not the middle, and they need to have different names. If you need to declare 2 CTEs you need to do so at the start, I.e. `WITH CTE1 AS ({SELECT statement}), CTE2 AS ({SELECT statement}) SELECT * FROM CTE1 EXCEPT SELECT * FROM CTE2;` — Thom A, Aug 06 '19 at 07:10
[WITH common_table_expression (Transact-SQL) - Using multiple CTE definitions in a single query](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017#c-using-multiple-cte-definitions-in-a-single-query) — Thom A, Aug 06 '19 at 07:11

score 1 · Accepted Answer · answered Aug 06 '19 at 07:07

your query would be like below

With cte as
    (select *, row_number() 
    over(partition by [Employee ID] order by [QTR] DESC,Wk DESC) rownumber 
    from tbl_HC
    ), cte1 as 
    (
     select *, row_number() 
    over(partition by [Employee ID] order by [QTR] DESC,Wk DESC) rownumber 
    from tbl_HC
    )
    select * from cte 
    where rownumber =1
    and QTR= (Select max(QTR) from tbl_SDS_Headcount_Manageby)
    except

    select * from cte1
    where rownumber =1
    and Wk= (
    Select max(Wk) from tbl_HC
    where QTR = (Select max(QTR) from tbl_HC))

Perfect! Thanks for the Solution – Punith GP Aug 06 '19 at 08:05 — Punith GP, Aug 06 '19 at 08:05

SQL Except Operator and with CTE to select all the items in 1st query but not in 2nd Query

1 Answers1