Thank you for SQLfiddle and sample data! I wish more questions started this way.
If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN
. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE
clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL
on that side (because it doesn't know if NULL
would match the filter or not).
I modified the first query to get a row for every member:
SELECT Members.Member_ID
,Time_Entry.Date_Start
,Time_Entry.Hours_Actual
,Time_Entry.Hours_Bill
FROM dbo.Members
LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
ON Members.Member_ID = Time_Entry.Member_ID
AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND
I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE
etc.
Some other notes:
You can use MAKEDATE()
, it makes the day_of_year
to a date.
Test:
mysql> SELECT * FROM cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
| 1 | 1 | 1 | 23.00 |
| 1 | 2 | 2 | 22.95 |
| 12 | 31 | 365 | 23.12 |
+-------+--------------+-------------+-------+
3 rows in set (0.00 sec)
mysql> SELECT
-> MAKEDATE(YEAR(CURDATE()),day_of_year) AS Date,
-> value AS DayValue
-> FROM test.cli_table;
+------------+----------+
| Date | DayValue |
+------------+----------+
| 2015-01-01 | 23.00 |
| 2015-01-02 | 22.95 |
| 2015-12-31 | 23.12 |
+------------+----------+
3 rows in set (0.00 sec)
mysql>
You can try it in this Fiddle.
EDIT:
According of what you want, you can make it with pure SQL:
mysql> SELECT * FROM test.cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
| 1 | 1 | 1 | 5.00 |
| 1 | 2 | 2 | 6.00 |
| 1 | 3 | 3 | 7.00 |
| 12 | 25 | 359 | 23.00 |
| 12 | 26 | 360 | 22.95 |
| 12 | 27 | 361 | 1.00 |
| 12 | 28 | 362 | 2.00 |
| 12 | 29 | 363 | 3.00 |
| 12 | 30 | 364 | 4.00 |
| 12 | 31 | 365 | 23.12 |
+-------+--------------+-------------+-------+
10 rows in set (0.00 sec)
mysql> SELECT
-> v.selected_date,
-> v.dayyear,
-> c.value AS DayValue,
-> c.day_of_year
-> FROM test.cli_table AS c
-> JOIN (SELECT
-> adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
-> DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
-> 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) AS v
-> ON (v.selected_date between '2015-12-25' AND '2016-01-03'
-> AND v.dayyear=c.day_of_year);
+---------------+---------+----------+-------------+
| selected_date | dayyear | DayValue | day_of_year |
+---------------+---------+----------+-------------+
| 2015-12-25 | 359 | 23.00 | 359 |
| 2015-12-26 | 360 | 22.95 | 360 |
| 2015-12-27 | 361 | 1.00 | 361 |
| 2015-12-28 | 362 | 2.00 | 362 |
| 2015-12-29 | 363 | 3.00 | 363 |
| 2015-12-30 | 364 | 4.00 | 364 |
| 2015-12-31 | 365 | 23.12 | 365 |
| 2016-01-01 | 1 | 5.00 | 1 |
| 2016-01-02 | 2 | 6.00 | 2 |
| 2016-01-03 | 3 | 7.00 | 3 |
+---------------+---------+----------+-------------+
10 rows in set (0.00 sec)
mysql>
Query Meaning:
SELECT
v.selected_date,
v.dayyear,
c.value AS DayValue,
c.day_of_year
FROM test.cli_table AS c
--- > This JOIN is generating all the dates between the selected_dates ('2015-12-25' AND '2016-01-03')
JOIN (SELECT
adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
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) AS v
--- > Here I'm matching every dayyear of the generated dates with the cli_table.day_of_year.
ON (v.selected_date between '2015-12-25' AND '2016-01-03'
AND v.dayyear=c.day_of_year);
Best Answer
It is possible that I am missing something, but you should be able to get the result easily using the
max()
aggregate and aGROUP BY
:See SQL Fiddle with Demo