How to query if record exists for prior month in mysql [closed]

Question

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

Answer 1

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.)

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)

Answer 3

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;