CODEWITHSUNDEEP
sqlsql-server-2008
Slee
Slee

Reputation: 28248

find Customers where none of the Orders have been Exported

I need to get a list of Customers who have never had an Order Exported

I am passing in a list of CustomerNumbers, grab them join on Orders then I am grouping - I feel like I am close but not sure how to get just Customers where none of the Orders.Exported is set to 1.

Here is what I have so far:

SELECT Customers.CustomerID,
       Orders.Exported,
       Count(Orders.OrderID) AS OrderCount
FROM Customers WITH (Nolock)
JOIN Orders ON Customers.ManufacturerID = Orders.ManufacturerID
AND Customers.CustomerNumber = Orders.CustomerNumber
WHERE Customers.CustomerNumber IN (
  SELECT *
  FROM dbo.Split(REPLACE(@CustomerNumbers,'\',''),','))
  AND Customers.ManufacturerID=@ManufacturerID
  AND Customers.Source = 'ipad'
GROUP BY Customers.CustomerID,
         Orders.Exported

This almost gets me what I need, my results for this are:

CustomerID  Exported    OrderCount
375408      NULL         1
375408      1            5
375412      1            2 
376892      NULL         1

So out of this list I would only want 376892 because they have never had an Order exported before

Upvotes: 1

Views: 49

Answers (3)

Kaf
Kaf

Reputation: 33839

You could use Having Min(IsNull(Orders.Exported,0)) with a Left Join and remove grouping by Orders.Exported to filter out customers who has exported orders before.

Logically your count will always be 0 and so you don't need to count.

SELECT Customers.CustomerID, Min(IsNull(Orders.Exported,0)) Exported, Count(Orders.OrderID) As OrderCount
FROM Customers With (Nolock) LEFT JOIN Orders 
    ON Customers.ManufacturerID = Orders.ManufacturerID AND 
       Customers.CustomerNumber = Orders.CustomerNumber
WHERE Customers.CustomerNumber IN (
         SELECT colName FROM dbo.Split(REPLACE(@CustomerNumbers,'\',''),',')) AND 
         Customers.ManufacturerID=@ManufacturerID AND Customers.Source = 'ipad' 
GROUP BY Customers.CustomerID 
HAVING Min(IsNull(Orders.Exported,0)) = 0

Upvotes: 1

adrianm
adrianm

Reputation: 14726

WITH CustomersWithExportedOrders AS (
    SELECT Customers.CustomerNumber
    FROM Customers
         INNER JOIN Orders
             ON Customers.ManufacturerID = Orders.ManufacturerID 
                AND Customers.CustomerNumber = Orders.CustomerNumber
    WHERE Orders.Exported IS NOT NULL
)
SELECT Customers.CustomerID
     , Orders.Exported
     , Count(Orders.OrderID) As OrderCount
FROM Customers 
     INNER JOIN Orders 
         ON Customers.ManufacturerID = Orders.ManufacturerID 
            AND Customers.CustomerNumber = Orders.CustomerNumber
WHERE Customers.CustomerNumber IN (SELECT * 
                                   FROM dbo.Split(REPLACE(@CustomerNumbers, '\', ''), ','))
      AND Customers.ManufacturerID = @ManufacturerID
      AND Customers.CustomerNumber NOT IN (SELECT CustomerNumber 
                                           FROM CustomersWithExportedOrders)
      AND Customers.Source = 'ipad' 
GROUP BY Customers.CustomerID
        , Orders.Exported

Upvotes: 0

abodvdv
abodvdv

Reputation: 99

WHERE Customers.CustomerNumber IN (SELECT * FROM dbo.Split(REPLACE(@CustomerNumbers,'\',''),','))
    AND Customers.ManufacturerID=@ManufacturerID
    AND Customers.Source = 'ipad' 
    AND Orders.Exported is NuLL

Upvotes: 0

Related Questions