How to find if a company (customer) ordered in a particular month and year and never before? (MySQL)

Question

This would be considered a "new shop open" in that particular month and year. Say company Store123 placed their first order in January, 2023 and it checks if Store123 ordered before that. If the company did not than Store123 would be considered a "new shop open" starting in January 2023.

I only have one table with Columns:

**Company	Order ID	Close Date	Amount**
Store123	00018972	2023-04-26	2880
StoreABC	00017237	2023-01-05	3720
Store123	00018242	2023-01-03	4280
StoreABC	00017123	2022-06-05	3231

This should return:

Store123 | 00018242 | 2023-01-03 | 4280

My logic is somewhat like this, even though this query isn't working

SELECT *
FROM Orders
WHERE MIN(Close Date) 
  BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY 'Company';

Answer 1

You can try the query below

-- New Shop Open
SELECT *
FROM Orders o
WHERE `Close Date` >= '2023-01-01' AND `Close Date` < '2023-02-01'
    AND NOT EXISTS (
        SELECT 1
        FROM Orders
        WHERE Company = o.Company AND `Close Date` < '2023-01-01' 
    )