1

I have a table with custId and orderDate fields, and I need a query to display whether or not the custId in that record has another record in the table with an order from the prior month. For example:

custId   orderDate    hasOrderInPriorMonth
aaa      2017-11-01   false
bbb      2017-12-15   false
aaa      2018-01-29   false
bbb      2018-01-15   true

3 Answers 3

3

Another way is to use variables to calculate the flag. The variables will store the values of custId and orderDate as well as the calculated flag value. All three will be used to calculate the subsequent row's flag.

You will need to sort the rows by custId, then by orderDate and implement the following logic to calculate the flag values:

  • if the current row's custId is different from the one stored in the corresponding variable (which at this point is carrying the previous custId), set the flag to false;

  • otherwise, if the year and month of the current row's orderDate match those of the date stored in the corresponding variable (which, similarly, is the date from the previous row), keep the previous flag value;

  • otherwise, set the flag to true if the current orderId minus one month is the same year and month as the date stored in the variable;

  • store the current custId and the year and month of the current orderId for use in the next iteration.

The MySQL-specific SQL query for that could be something like this:

SELECT
  custId,
  orderDate,
  flag AS hasOrderInPriorMonth
FROM
  (
    SELECT
      @flag :=
        CASE
          WHEN o.custId <> @custId THEN false
          WHEN EXTRACT(YEAR_MONTH FROM o.orderDate) = @orderMonth THEN @flag
          ELSE (EXTRACT(YEAR_MONTH FROM o.orderDate - INTERVAL 1 MONTH) = @orderMonth)
        END AS flag,
      @custId := o.custId as custId,
      o.orderDate,
      @orderMonth := EXTRACT(YEAR_MONTH FROM o.orderDate)
    FROM
      (SELECT @custId := '', @orderMonth := 0, @flag := false) as x,
      orders as o
    ORDER BY
      custId    ASC,
      orderDate ASC
  ) AS s
;

A demo of this query is available at SQL Fiddle. (It borrows the test setup created by Jehad Keriaki for his own answer.)

2

TIMESTAMPDIFF function is useful here:

[mysql] desc orders;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| custId    | char(10) | YES  | MUL | NULL    |       |
| orderDate | date     | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

[mysql] select * from orders;
+--------+------------+
| custId | orderDate  |
+--------+------------+
| aaa    | 2017-11-01 |
| bbb    | 2017-12-15 |
| aaa    | 2018-01-29 |
| bbb    | 2018-01-15 |
+--------+------------+
4 rows in set (0.00 sec)

[mysql] select 
    o.custId, 
    o.orderDate, 
    IF((select count(*) from orders o2 
        where o.custId=o2.custId and 
        timestampdiff(MONTH, o2.orderDate, o.orderDate)=1) >= 1, 
        "TRUE", "FALES") 
    as hasOrderInPriorMonth 
from orders o;
+--------+------------+----------------------+
| custId | orderDate  | hasOrderInPriorMonth |
+--------+------------+----------------------+
| aaa    | 2017-11-01 | FALES                |
| bbb    | 2017-12-15 | FALES                |
| aaa    | 2018-01-29 | FALES                |
| bbb    | 2018-01-15 | TRUE                 |
+--------+------------+----------------------+
4 rows in set (0.00 sec)
4
  • Test: sqlfiddle.com/#!9/981608/1 Feb 7, 2018 at 21:34
  • 1
    Why (select count(*) from ...) = 1 rather than exists (select * from ...)? I'm asking that generally but there's also a more specific concern: what if a customer can have more than one order per month? Relying on count(*) = 1 could then give you incorrect results.
    – Andriy M
    Feb 8, 2018 at 10:00
  • Right @AndriyM I changed it to ">=" Feb 8, 2018 at 18:23
  • This gives you the first of last month: CURDATE() - INTERVAL DAYOFMONTH(CURDATE())-1 DAY - INTERVAL 1 MONTH
    – Rick James
    Feb 14, 2018 at 22:36
0

Join this table back to itself:

SELECT ordersA.custId,
ordersA.orderDate AS thisOrderDate,
COUNT(ordersB.orderDate) AS priorMonthOrders
FROM orders AS ordersA LEFT OUTER JOIN orders AS ordersB
ON ordersA.custId = ordersB.custId
AND ordersB.orderDate >= date_sub(ordersA.orderDate, INTERVAL 1 MONTH)
AND ordersB.orderDate < ordersA.orderDate
GROUP BY ordersA.custId, ordersA.orderDate;
5
  • I think if there is more than one order to the same client in the previous month, this query will give you duplicates Feb 7, 2018 at 20:22
  • Great point, Jehad. I added a GROUP BY and just return a count now to avoid that issue. (Also was missing the custId match in the join, but that was probably obvious.) Thanks! Feb 7, 2018 at 21:21
  • In fact, there is another problem. It could be that the difference is 35 days, yet it is in the previous month. This query will ignore this case. Feb 7, 2018 at 21:29
  • I suppose that's true, though if that's a problem probably depends on what CraigV meant by "an order from the prior month". I could see several interpretations of that phrase. If it is a problem, wrap those with MONTH() to reduce them to the first of the respective months and essentially flatten the day-in-month aspect of the fields. Feb 7, 2018 at 21:32
  • This is useful to test the queries: sqlfiddle.com/#!9/981608/2 Feb 7, 2018 at 21:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.