sql - Create a query that will give me all the customers that do not have an order in the last 12 months - Stack Overflow

admin管理员组

文章数量:1122832

I am trying to create a query that will give me all the customers that did not place an order in the last 12 months.

SELECT  
    so.Customer_ID, c.Loginname, COUNT(*) AS cou
FROM 
    [Mouser_UVDATA].[dbo].[SalesOrder_Header] so     
INNER JOIN
    Mouser_Com.dbo.Customer c ON so.Customer_Id = c.Customer_Id
WHERE
    so.OrderDate >= DATEADD(YEAR, -1, GETDATE()) 
    AND so.OrderDate < DATEADD(day, 1, GETDATE())
GROUP BY
    so.Customer_ID, c.Loginname
HAVING
    COUNT(*) = 0

And I am going insane:

WHERE 
    so.OrderDate >= DATEADD(YEAR, -1, GETDATE()) 
    AND so.OrderDate < DATEADD(day, 1, GETDATE())

Will select the orders in the last year. Which means if the user doesn't have any HAVING COUNT(*) = 0 doesn't return any result.

Do you have any idea how to invert the query somehow? Honestly I do not see a way.

I am trying to create a query that will give me all the customers that did not place an order in the last 12 months.

SELECT  
    so.Customer_ID, c.Loginname, COUNT(*) AS cou
FROM 
    [Mouser_UVDATA].[dbo].[SalesOrder_Header] so     
INNER JOIN
    Mouser_Com.dbo.Customer c ON so.Customer_Id = c.Customer_Id
WHERE
    so.OrderDate >= DATEADD(YEAR, -1, GETDATE()) 
    AND so.OrderDate < DATEADD(day, 1, GETDATE())
GROUP BY
    so.Customer_ID, c.Loginname
HAVING
    COUNT(*) = 0

And I am going insane:

WHERE 
    so.OrderDate >= DATEADD(YEAR, -1, GETDATE()) 
    AND so.OrderDate < DATEADD(day, 1, GETDATE())

Will select the orders in the last year. Which means if the user doesn't have any HAVING COUNT(*) = 0 doesn't return any result.

Do you have any idea how to invert the query somehow? Honestly I do not see a way.

• sql

Share Improve this question Follow edited Nov 22, 2024 at 10:56 jarlh 44.6k88 gold badges5050 silver badges6767 bronze badges asked Nov 21, 2024 at 15:51 Tania MarinovaTania Marinova 1,89888 gold badges4242 silver badges6767 bronze badges 3

• 1 You didn't tag your DBMS, but using NOT EXISTS or EXCEPT is one way for achieving this. – Isolated Commented Nov 21, 2024 at 15:55
• Can you provide a sample query – Tania Marinova Commented Nov 21, 2024 at 15:59
• Skip the WHERE clause, instead do HAVING MAX(DATE) < today minus one year. – jarlh Commented Nov 21, 2024 at 18:27

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 3

You just need to use a not exists. The way you have now you are essentially querying orders and customers where order date is one year old.

You want customers, so you query that. Also why are you using an aggregate function as well if you need a list of customers?

SELECT  c.Customer_Id, c.Loginname
  FROM Mouser_Com.dbo.Customer c
 where NOT EXISTS (select 1 
                     from [Mouser_UVDATA].[dbo].[SalesOrder_Header] so
                    where so.OrderDate >= dateadd(YEAR, -1, getdate())
                      and so.OrderDate < dateadd(day, 1, getdate())
                      and so.Customer_Id = c.Customer_Id
                  )

The select 1 is just a return value, essentially not exists is checking whether that query returns anything at all if it does it excludes it from the result.

Not knowing your data and database - used year as number - you should adjust it to your context using real date columns and 12 month periods instead of the year...

--      S a m p l e    D a t a :  
Create Table Customers AS
Select 1 as CustId, 'Customer A' as CustName Union All
Select 2, 'Customer B' Union All
Select 3, 'Customer C' Union All
Select 4, 'Customer D' Union All
Select 5, 'Customer E';
 
Create  Table Orders AS 
Select 101 as OrdId, 2023 as Ordyear, 1 as CustId Union All 
Select 102, 2023, 2 Union All
Select 103, 2023, 4 Union All
Select 104, 2024, 1 Union All
Select 105, 2024, 2 Union All
Select 106, 2024, 3 Union All
Select 107, 2024, 4;

Aggregate (Count) Orders per Customer and Year - Left Join it to Customers and filter the result with ON conditions and Where clause ...

--      S Q L :  
Select c.* 
From Customers c
Left Join  ( Select   CustId, OrdYear, 
                      Count(o.OrdId) as Cnt
             From     Orders o 
             Group By CustId, OrdYear
           ) o ON( o.CustId = c.CustId And o.OrdYear = 2023 ) 
Where o.CustId Is Null;

R e s u l t :

custid	custname
3	Customer C
5	Customer E

fiddle

Another example

SELECT  c.Customer_ID, c.Loginname
FROM Mouser_Com.dbo.Customer c
WHERE c.Customer_ID not in
    (select distinct so.Customer_id 
     from [Mouser_UVDATA].[dbo].[SalesOrder_Header] so
     where 
         so.OrderDate >= DATEADD(YEAR, -1, GETDATE()) 
      AND so.OrderDate < DATEADD(day, 1, GETDATE())
  )

SELECT s.Customer_ID
, c.Loginname
FROM Mouser_Com.dbo.Customer c
INNER JOIN [Mouser_UVDATA].[dbo].[SalesOrder_Header] s
    ON s.Customer_Id = c.Customer_Id
WHERE NOT EXISTS (
        SELECT NULL
        FROM [Mouser_UVDATA].[dbo].[SalesOrder_Header] so
        WHERE so.OrderDate >= dateadd(YEAR, - 1, getdate())
            AND so.OrderDate < dateadd(day, 1, getdate())
            AND so.Customer_Id = c.Customer_Id
        )

本文标签：