Im trying to create a schedule table per week of year with MySQL
I have a table with the following Sample data:
| employee | start_date | end_date | weekday | shift_start | shift_end |
|---|---|---|---|---|---|
| 1 | 2021-10-01 | 2021-10-31 | 0 | 08:00:00 | 17:00:00 |
| 1 | 2021-10-01 | 2021-10-31 | 2 | 08:00:00 | 17:00:00 |
| 1 | 2021-10-01 | 2021-10-31 | 4 | 08:00:00 | 17:00:00 |
| 2 | 2021-10-01 | 2021-10-31 | 0 | 08:00:00 | 17:00:00 |
| 2 | 2021-10-01 | 2021-10-31 | 3 | 08:00:00 | 17:00:00 |
| 2 | 2021-10-01 | 2021-10-31 | 4 | 08:00:00 | 17:00:00 |
The schedule is the same every week, hence the weekday column.
I also have a calendar table for simplicity with dates for coming 2 years with the following schema:
| fulldate | year | month | day | week | dayofweek |
|---|---|---|---|---|---|
| 0000-00-00 | 0000-9999 | 0-11 | 0-30 | 0-53 | 0-6 |
I want to have a table output like the following for a certain weeknumber: (example week 40 of 2021)
| date | employee | weekday | shift_start | shift_end |
|---|---|---|---|---|
| 2021-10-04 | 1 | 0 | 08:00:00 | 17:00:00 |
| 2021-10-04 | 2 | 0 | 08:00:00 | 17:00:00 |
| 2021-10-06 | 1 | 2 | 08:00:00 | 17:00:00 |
| 2021-10-07 | 2 | 3 | 08:00:00 | 17:00:00 |
| 2021-10-08 | 1 | 4 | 08:00:00 | 17:00:00 |
| 2021-10-08 | 2 | 4 | 08:00:00 | 17:00:00 |
I have been stuck on this for a while and I haven't found a solution on the internet or made a single step in creating the table. Any help in the right direction would be greatly appreciated
CodePudding user response：
Solution for your problem is:
SELECT
c.fulldate as date,
t.employee,
t.weekday,
t.shift_start,
t.shift_end
FROM calendar AS c
INNER JOIN table1 AS t
ON c.dayofweek = t.weekday
ORDER BY c.fulldate,t.employee,t.weekday
Demo Link: http://sqlfiddle.com/#!9/4c8681/4
Explanation:
Calendar table should be inner joined with input table on weekday column to get those dates for which result is required.
'WHERE' clause can be used to filter out the result as per the requirement. For example, if output is required for 40th week then use "WHERE c.week=40" in above query before 'ORDER BY' clause to get desired result.