Find rows with consecutive dates

Question

Is it possible to know which customer came at the bar 3 consecutive days? (john in my case)

Thanks in advance

Name	Age	Date	Amount
Paul	12	2021-12-01	20
John	19	2021-12-01	10
Jack	17	2021-13-01	7
John	19	2021-13-01	8
John	19	2021-14-01	17

Yes, that's possible, what have you tried to find the answer? — Luuk, Mar 02 '22 at 13:51
See this: https://stackoverflow.com/questions/26117179/sql-count-consecutive-days — Samy Sammour, Mar 02 '22 at 13:53
I've tried: SELECT Name FROM Customer GROUP BY Name HAVING MAX(Date)-MIN(Date)=2days and COUNT(Date)=3 — Aymeric Leboucher, Mar 02 '22 at 13:54
Your query returns `John` (see: [DBFIDDLE](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4e5d5ddf91c319b5d1b250a453c88e0a)), so it is unclear what your question is. — Luuk, Mar 02 '22 at 13:59
BTW: A proper date format is given in [YYYY-MM-DD](https://en.wikipedia.org/wiki/ISO_8601), not in `YYYY-DD-MM` as those Americans tend to do , to make it readable to everyone. — Luuk, Mar 02 '22 at 14:03

score 0 · Answer 1 · edited Mar 02 '22 at 14:52

0

so I would approach this by

SELECT FROM Table
    LEFT JOIN Table As PrevDay on PrevDay.Customer = Table.Customer 
        AND PrevDay.date = DATEADD(DAY,-1,Table.Date)
    LEFT JOIN Table AS NextDay on NextDay,Customer = Table.Customer 
        AND NextDay.Date = DATEADD(DATE,1,Table.Date)
WHERE PrevDay.Customer is not NULL 
AND NextDay.Customer is not null

edited Mar 02 '22 at 14:52

RiggsFolly

93,638
21
103
149

answered Mar 02 '22 at 14:10

Aaron Reese

544
6
18

forpas · Answer 2 · 2022-03-02T15:14:36.287

Assuming that the data type of the column Date is DATE you can use a self join and aggregation:

SELECT DISTINCT t1.Name
FROM tablename t1 INNER JOIN tablename t2
ON t2.Name = t1.Name AND ABS(DATEDIFF(t1.Date, t2.Date)) = 1
GROUP BY t1.Name, t1.Date
HAVING COUNT(DISTINCT t2.Date) = 2;

Or, a more scalable solution:

SELECT DISTINCT t1.Name
FROM tablename t1 INNER JOIN tablename t2
ON t2.Name = t1.Name AND t2.Date BETWEEN t1.Date AND t1.Date + INTERVAL 2 DAY
GROUP BY t1.Name, t1.Date
HAVING COUNT(DISTINCT t2.Date) = 3;

You can remove DISTINCT from COUNT(DISTINCT t2.Date) if the combination of Name and Date is unique in the table.

See the demo.

Find rows with consecutive dates

2 Answers2