Let's say I have a table (tbl_reservations) with the following columns:
p_id
userid
reservation_id
book_date
db_add_date
p_id userid reservation_id book_date
1 01 124534 2016-02-19 2016-02-19
2 01 124554 2016-02-20 2016-02-20
3 01 124564 2016-02-21 2016-02-21
4 01 167823 2016-02-22 2016-02-22
5 01 12313 2016-02-23 2016-02-23
6 02 134564 2016-02-21 2016-02-21
7 03 197823 2016-02-22 2016-02-22
8 02 13313 2016-02-23 2016-02-23
**Desired Result**
As userid 01 booked reservation since last 5 days regularty it will appear in query result
userid
01
I want to get users who booked a certain number of days in a row ?
e.g. I want to get unique users who booked in last 5 days
Can anyone help me how to build a query for this? It would be a really big help. Thank you.
If you want unique users who booked in the last five days, then a query like this would seem to do what you want:
select userid
from tbl_reservations
where bookdate > date_sub(curdate(), interval 5 day) and bookdate <= curdate()
group by userid;
If you want users who booked on all the days:
select userid
from tbl_reservations
where bookdate > date_sub(curdate(), interval 5 day) and bookdate <= curdate()
group by userid
having count(*) = 5;
Try this query:
SELECT userid
from tbl_reservations
WHERE book_date >= DATE_SUB(CURDATE(), INTERVAL 5 DAY) AND book_date <= CURDATE()
GROUP BY userid
HAVING COUNT(*) = 5;
try this sql.You get Last % days who booked list from current date
select count(userid), userid
from tbl_reservations
where (book_date > DATE_ADD(CURDATE(), INTERVAL -5 DAY) and book_date<=CURDATE())
group by userid
having count(*) = 5;