I want to count total rows of 4 weeks in month and group it by location the results should be like:
location week1 week2 week3 week4
Floor A 10 0 3 4
Floor B 5 2 0 0
...
I've tried this to get a week number by given a date.
CASE WHEN DAY(create_date) >= 1 AND DAY(create_date) < 8 THEN 1
WHEN DAY(create_date) >= 8 AND DAY(create_date) < 15 THEN 2
WHEN DAY(create_date) >= 15 AND DAY(create_date) < 22 THEN 3
ELSE 4 END AS WeekNumber
It's gives me this result:
+----+-----------+--------------+------------+
| id | create_by | create_date | WeekNumber |
+----+-----------+--------------+------------+
| 1 | 555001 | '2018-11-01' | 1 |
| 2 | 555002 | '2018-11-05' | 1 |
| 3 | 555004 | '2018-11-06' | 1 |
| 4 | 555001 | '2018-11-10' | 2 |
| 5 | 555003 | '2018-11-17' | 3 |
| 6 | 555002 | '2018-11-17' | 3 |
| 7 | 555001 | '2018-11-18' | 3 |
| 8 | 555003 | '2018-11-20' | 3 |
| 9 | 555001 | '2018-11-22' | 4 |
| 10 | 555001 | '2018-11-25' | 4 |
+----+-----------+--------------+------------+
My tables
tbl_user
+----+----------+----------+
| id | username | location |
+----+----------+----------+
| 1 | 555001 | Floor A |
| 2 | 555002 | Floor B |
| 3 | 555003 | Floor C |
| 4 | 555004 | Floor A |
| 5 | 555005 | Floor C |
+----+----------+----------+
tbl_list
+----+-----------+--------------+
| id | create_by | create_date |
+----+-----------+--------------+
| 1 | 555001 | '2018-11-01' |
| 2 | 555002 | '2018-11-05' |
| 3 | 555004 | '2018-11-06' |
| 4 | 555001 | '2018-11-10' |
| 5 | 555003 | '2018-11-17' |
| 6 | 555002 | '2018-11-17' |
| 7 | 555001 | '2018-11-18' |
| 8 | 555003 | '2018-11-20' |
| 9 | 555001 | '2018-11-22' |
| 10 | 555001 | '2018-11-25' |
+----+-----------+--------------+