DateDiff in MySQL with same column

Question

I have a table 'orders' that looks like this:

+---------------+--------------+------------+
| customer_name | order_number |    date    |
+---------------+--------------+------------+
| jack          |            1 | 2018-01-01 |
| jack          |            2 | 2018-01-06 |
| jack          |            3 | 2018-01-19 |
| jack          |            4 | 2018-01-06 |
| jack          |            5 | 2018-02-27 |
| jack          |            6 | 2018-02-02 |
+---------------+--------------+------------+

Now, I want a table that gives me the difference in the consecutive dates in days. Like so:

+------------+------------+------+
|    date    | next_date  | diff |
+------------+------------+------+
| 2018-01-01 | 2018-01-06 |    5 |
| 2018-01-06 | 2018-01-06 |    0 |
| 2018-01-06 | 2018-01-19 |   13 |
| 2018-01-19 | 2018-02-02 |   14 |
| 2018-02-02 | 2018-02-27 |   25 |
+------------+------------+------+

The query I used is this:

SELECT orders.date, MIN(table1.date) FROM orders
    LEFT JOIN orders table1
    on orders.customer_name = table1.customer_name
    AND table1.date >= orders.date
    AND table1.order_number !=  orders.order_number
    WHERE orders.customer_name = 'jack'
    GROUP BY orders.order_number, orders.date
    ORDER BY orders.date;

This is the ouptput:

+------------+------------+
|    date    | next_date  |
+------------+------------+
| 2018-01-01 | 2018-01-06 |
| 2018-01-06 | 2018-01-06 |
| 2018-01-06 | 2018-01-06 |
| 2018-01-19 | 2018-02-02 |
| 2018-02-02 | 2018-02-27 |
| 2018-02-27 |    NULL    |
+------------+------------+

As you can see there are a a few issues.

1. There are two rows where the date and next_date are both 2018-01-06.
2. There is no row where the next_date is 2018-01-19`
3. The last row has a NULL value for next_date
4. How do I get difference in date in days?

I know this is happening because I have grouped by order_number and >= but I don't know how else to approach this. I feel like there is an obvious easy solution to this that is evading me. Any help?

SQL Fiddle

In case SQL Fiddle doesn't work:

CREATE TABLE orders
    (`customer_name` varchar(4), `order_number` int, `date` varchar(10))
;

INSERT INTO orders
    (`customer_name`, `order_number`, `date`)
VALUES
    ('jack', 1, '2018-01-01'),
    ('jack', 2, '2018-01-06'),
    ('jack', 3, '2018-01-19'),
    ('jack', 4, '2018-01-06'),
    ('jack', 5, '2018-02-27'),
    ('jack', 6, '2018-02-02')
;

Answer 1

If you are interested on a non ROW_NUMBER() solution depending on MySQL 8.x or greater, check next explanation.

EXPLANATION:

1) First, we select all dates from the orders table, ordering they by ascending and assigning a virtual auto-incrementet ID to each date. We going to get something like this:

SELECT (@row_number := @row_number + 1) AS orderNum, date 
FROM ORDERS, (SELECT @row_number:=0) AS t
ORDER BY date;

Output:
1   2018-01-01
2   2018-01-06
3   2018-01-06
4   2018-01-19
5   2018-02-02
6   2018-02-27

2) We create a similar query to the previous one, but this time we discard the first row, like this:

SELECT (@row_number2 := @row_number2 + 1) AS orderNum, date
FROM ORDERS, (SELECT @row_number2 := 0) AS t
ORDER BY date
LIMIT 999999999999
OFFSET 1;

Output:
1   2018-01-06
2   2018-01-06
3   2018-01-19
4   2018-02-02
5   2018-02-27

The only problem here, is we have to hardcode the LIMIT number to a sufficiently high number, so we can assure we will select all rows except the first one.

3) At this point, you should be thinking on joining the both previous results by the virtually generated ID. So, lets see the final query:

SELECT
    startDate.date AS date,
    nextDate.date AS next_date,
    DATEDIFF(nextDate.date, startDate.date) AS diff
FROM
    (SELECT (@row_number := @row_number + 1) AS orderNum, date 
     FROM ORDERS, (SELECT @row_number:=0) AS t
     ORDER BY date) AS startDate
INNER JOIN
    (SELECT (@row_number2 := @row_number2 + 1) AS orderNum, date
     FROM ORDERS, (SELECT @row_number2 := 0) AS t
     ORDER BY date
     LIMIT 999999999999
     OFFSET 1) AS nextDate ON nextDate.orderNum = startDate.orderNum;

Output:
2018-01-01  2018-01-06  5
2018-01-06  2018-01-06  0
2018-01-06  2018-01-19  13
2018-01-19  2018-02-02  14
2018-02-02  2018-02-27  25

You can see the working example here: http://sqlfiddle.com/#!9/1572ea/27

Answer 2

• There is no direct correlation between order_number and date. Even higher value of order_number is having lower date.
• So, we cannot use order_number to determine next_date.
• Since your MySQL version is 8.0, we can do a smart utilization of Row_Number() functionality.
• We will use the orders table twice into two different Derived Tables, with row numbers assigned based on ascending order of the date. The difference between the two will be that one of the table will have modified row numbers (incremented by 1).
• Now, all we need to do is inner join between them, based on the row number values. Due to this "sliding gap" between the row numbers, we will get "current" and "next" date. Inner join will also ensure that the last "current" date row where there is no matching "next" date, will not come.
• Eventually, we can use the DateDiff() function to determine the difference between the dates.

Try the following:

SELECT t.`date`, 
       next_t.`date` AS next_date, 
       DATEDIFF(next_t.`date`, t.`date`) AS diff 
FROM 
(
  SELECT 1 + (ROW_NUMBER() OVER (ORDER BY `date` ASC)) AS rn, 
          `date`  
  FROM orders 
  WHERE customer_name = 'jack'
) AS t

JOIN 

(
  SELECT (ROW_NUMBER() OVER (ORDER BY `date` ASC)) AS rn, 
          `date`  
  FROM orders 
  WHERE customer_name = 'jack'
) AS next_t ON next_t.rn = t.rn

DB Fiddle DEMO

Answer 3

You can do it this way. But your order 4 has a previous date than order 3. So it will yield a negative.

SELECT customer_name,order_number,date,
LEAD(date) OVER (ORDER BY customer_name,order_number) next_date,
ISNULL(DATEDIFF(DAY,date,LEAD(date) OVER (ORDER BY customer_name,order_number)),0) AS diff
FROM #orders