How to group date between two past date in one column MYSQL

Question

I have this Query

SELECT
DATE_ADD(table1.date, INTERVAL -7 DAY) AS date7,
DATE_ADD(table1.date, INTERVAL -14 DAY) AS date14,
DATE_ADD(table1.date, INTERVAL -21 DAY) AS date21,
table1.hotel AS hotel,
table1.customer AS Customer,
table1.date AS Date,
table1.sell AS Sells,
table2.adr AS ADR,
table2.revpar AS RevPAR,
table3.name_hotel AS Hotel_name,
FROM `table1`
INNER JOIN table3 ON table1.hotel=table3.hotel
INNER JOIN table2 
ON table1.hotel=table2.hotel
AND table1.date_occ=table2.date_occ

I would like to get a column "weeks" where Date in between Date and date7 then "last week", Date between date7 and date14 then "-2 week" and Date between in date14 and date21 then "-3week". Because I need to group in "weeks" to calculate sells last 7 days, 14 days and 21 days according current date

Does this answer your question? [How to group by week in MySQL?](https://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql) — nbk, May 13 '20 at 16:33
In mysql you have several function for date year() week() ,,month() — ScaisEdge, May 13 '20 at 16:51

score 1 · Accepted Answer · answered May 13 '20 at 18:37

Something like this should work.

table1.date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
table1.date BETWEEN DATE_SUB(NOW(), INTERVAL 14 DAY) AND DATE_SUB(NOW(), INTERVAL 7 DAY)
table1.date BETWEEN DATE_SUB(NOW(), INTERVAL 21 DAY) AND DATE_SUB(NOW(), INTERVAL 14 DAY)

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-sub

How to group date between two past date in one column MYSQL

1 Answers1