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

Thanks in advance

CodePudding user response：

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;

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

See the demo.

CodePudding user response：

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