I am using the following query to get a list of dates between two given dates:
SELECT selected_date FROM
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11';
This gives me the following output:
+---------------+
| selected_date |
+---------------+
| 2016-03-08 |
| 2016-03-09 |
| 2016-03-10 |
| 2016-03-11 |
+---------------+
The following query gives me a list of all employees required to work for a particular date:
SELECT employee.id, employee.firstname
FROM Users employee
LEFT JOIN Table1 table1 ON table1.fId = employee.tId
WHERE table1.day = dayofweek('2016-03-10')
This yields:
+----+-----------+
| id | firstname |
+----+-----------+
| 1 | Mat |
| 2 | Tom |
+----+-----------+
The results are empty if no employee is required to work on the given date.
I have a thrid table which lists the performance of each employee for a particular day:
SELECT * FROM performance;
+----+------------+------------+------+
| id | employeeId | date | val |
+----+------------+------------+------+
| 1 | 1 | 2016-03-08 | 100 |
| 2 | 2 | 2016-03-08 | 90 |
| 3 | 1 | 2016-03-09 | 80 |
| 4 | 2 | 2016-03-09 | 100 |
+----+------------+------------+------+
I want to join the above three results to yield:
+----+------------+-----------+------------+------+
| id | employeeId | firstname | date | val |
+----+------------+-----------+------------+------+
| 1 | 1 | Mat | 2016-03-08 | 100 |
| 2 | 2 | Tom | 2016-03-08 | 90 |
| 3 | 1 | Mat | 2016-03-09 | 80 |
| 4 | 2 | Tom | 2016-03-09 | 100 |
| 5 | 1 | Mat | 2016-03-10 | null |
| 6 | 2 | Tom | 2016-03-10 | null |
+----+------------+-----------+------------+------+
Note : On 2016-03-10
Mat
and Tom
aren't required to work. Hence, their values should be null in the final result. The performance
table will not have entries for Mat and Tom for 2016-03-10
in this case.
I am kind of lost on how to achieve this.