SQL Server : AdventureWorks Need HELP! Select Customers where order date is

Question

I'm working with the AdventureWorks2017 database.

I need to select customerID, year, and ordersQTY for clients that ordered in 2011 and 2014, but not in 2012 or 2013.

I've tried to code it with where clause but it doesn't seem to work right...

WITH SalesQTY AS
(
    SELECT 
        CustomerID, YEAR(OrderDate) Y
    FROM 
        Sales.SalesOrderHeader oh
    WHERE 
        YEAR(OrderDate) IN (2011, 2014)
)
SELECT CustomerID, Y, COUNT(Y)
FROM SalesQTY s
GROUP BY CustomerID, Y;

Answer 1

You can use not exists to do this.

SELECT CustomerID, YEAR(OrderDate),COUNT(*)
FROM Sales.SalesOrderHeader oh
WHERE YEAR(OrderDate) IN (2011, 2014)
AND NOT EXISTS (SELECT 1
                FROM Sales.SalesOrderHeader oh1
                WHERE oh.CustomerID=oh1.CustomerID
                AND YEAR(oh1.OrderDate) IN (2012, 2013)
               )
GROUP BY CustomerID, YEAR(OrderDate)
HAVING COUNT(DISTINCT YEAR(OrderDate)) = 2