Question 213001 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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';
Tags:

3

Answers


  1. 0

    The easiest way is to use a partitioning function ROW_NUMBER().

    https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/

    What you can do is simply partition by the "group by" in your case and order by the date. This way anything that returns as a value of 1 is the first instance of the store being active.

    If you need to filter via a date situation, you can then take your table of data and join back to it to get your answer.

    Something like this:

    SELECT 
    * 
FROM 
    CompanyTable T 
INNER JOIN (
    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY COMPANY ORDER BY CLOSE_DATE) AS RN 
    FROM 
        CompanyTable ) X 
    ON X.Company = T.Company 
    AND X.RN = 1 
WHERE 
    T.CLOSE_DATE BETWEEN <startdate> and <enddate>

    Please note, since I’m not 100% sure what you’re aiming for the code above is just a rough estimate to the answer. Good luck!

    Login or Signup to reply.
  2. 0

    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' 
    )
    Login or Signup to reply.
  3. 0

    Its probably because you cant combine the MIN function with the BETWEEN operator. Answers in SQL always return sets, not single results, that’s what is always to be taken into account. So the first thing is: you might want to know the Company, and then you could think about that there are no orders before that particular month.
    The other thing to remember is, SQL engines work differently. Oracle likes to get queries with "where not exists" and outer joins, while SQL-Server likes embedded subqueries. Mysql on the other hand more likely selects along ordering and eliminating rows.
    So it really depends on the server you are using. There is no solution that will work everywhere with any ammount of data. sorry.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search