Show all customers who havent placed an order in a specific time frame

Question

Fairly new to SQL and databases.... I have the following issue... Database for a shipping company, three relevant tables for this problem are;

Customer (CustomerID, Name, Address etc)

Shipment (ShipmentID, ScheduleName, DepartDate, ArriveDate, DepartPort, ArrivePort)

CustomerOrder (CustomerID, ShipmentID, Fee etc)

I need to show 'All customers that have NOT placed an order within a specific year, i.e. 2019' Im struggling to find the correct query for this. Im working on sample data, there are 8 customers in the database, 3 of which have orders for shipments departing in 2019. Therefore the result im hoping to get is a list of the 5 remaining customers who did not have orders on these shipments.

I can easily show customer who have placed an order and those who have never placed an order however im struggling to show those who haven't placed an order in a specific timeframe.

Any ideas or tips would be greatly appreciated!

Thanks

EDIT***

Desired Result - Show the 5 customers who havent placed an order where the shipping depart date is in 2019

I have tried;

~ SELECT CustomerID from customer LEFT OUTER JOIN customerorder ON Customer.CustomerID = CustomerOrder.CustomerID LEFT OUTER JOIN shipment ON CustomerOrder.ShipmentID = Shipment.ShipmentID WHERE shipment.DepartDate BETWEEN '2019-01-01' AND '2019-12-31' AND CustomerOrder.CustomerID IS NULL ~

However this bring back no results.

The three tables have the following information

Customer - (simply table of customer details) (CustomerID, Name, Address, Tel, Type, Size, RegisteredSince)

Shipment - (Details each shipment scheduled) (ShipmentID, ScheduleName, DepartDate, DepartPort, ArriveDate, ArrivePort, Season)

CustomerOrder - (Details customer order, but not individual items on an order) (OrderID, ShipmentID, CustomerID)

Im not sure if its the join thats the problem? There are 4 shipments in 2019 in the sample data with a total of 3 customers. I need to show the customerID's of the 5 customers who didnt place an order within these dates.

Ive tried a few different queries, have been searching online but im new to this and not quite sure where im going wrong. I am able to identify customers who have never placed an order but as soon as I add the date ranges i get no results.

Answer 1

Desired Result - Show the 5 customers who havent placed an order where the shipping depart date is in 2019

Use NOT EXISTS:

SELECT c.* 
FROM Customer c
WHERE NOT EXISTS (SELECT 1
                  FROM CustomerOrder co JOIN 
                       Shipment sh
                       ON co.shipmentID = sh.ShipmentID
                  WHERE c.CustomerID = co.CustomerID AND
                       sh.DepartDate >= '2019-01-01' AND
                       sh.DepartDate < '2020-01-01'
                 );

Answer 2

I don't now from your relations where do you save the order date, but you could change anything if it is needed. I have used DepartDate to mange orders Year that are different from 2019: Example:

SELECT c.* 
FROM Customer c
INNER JOIN CustomerOrder co ON c.CutomerID = co.CutomerID
INNER JOIN Shipment sh ON co.shipmentID = sh.ShipmentID
WHERE YEAR(sh.DepartDate) <> 2019

Get all cutomers data where DepartDate not equal to 2019

Answer 3

I think this could be the solution that you want, here is full-query. Sub-Query means you get all customerID-s that made orders on 2019 and we are exclude theme with not in ...

SELECT c.* 
FROM Customer c
INNER JOIN CustomerOrder co ON c.CutomerID = co.CutomerID
INNER JOIN Shipment sh ON co.shipmentID = sh.ShipmentID
WHERE YEAR(sh.DepartDate) <> 2019 AND  c.CutomerID 
  NOT IN (SELECT c.* 
          FROM Customer c
          INNER JOIN CustomerOrder co ON c.CutomerID = co.CutomerID
          INNER JOIN Shipment sh ON co.shipmentID = sh.ShipmentID
          WHERE YEAR(sh.DepartDate) = 2019)

Answer 4

Just do a left outer join on orders by customerId in the appropriate date range where the customerID doesn't exist:

select c.* 
from customers c
    left outer join (
        select customerID 
        from CustomerOrder o 
            join Shipment s on s.ShipmentID=o.ShipmentID 
        where DepartDate>='2020-01-01' and DepartDate<'2021-01-01'
    ) x on x.customerID=c.customerID  
where x.customerid is null

The subquery selects every single customerID who ordered in the date range. The left outer join and "customerid is null" does a negative match on those customers who don't exist in our subquery.