List:General Discussion« Previous MessageNext Message »
From:shaun thornburgh Date:October 20 2004 12:28pm
Subject:SELECTING Non existing Dates
View as plain text  
Hi,

I have query that I am using to try to get the number of hours worked and on 
which project by an employee during a particular week. Here is the query:

SELECT T.Date, T.Hours, P.Project_Name
FROM Timesheets T
LEFT JOIN Projects P ON P.Project_ID = T.Project_ID
WHERE T.Date >= "2004-10-18"
AND T.Date <= "2004-10-24"
AND T.User_ID = "1"
AND T.Type = "Project"
GROUP BY T.Date, T.Project_ID
ORDER BY T.Date

And here is the result:

+------------+-------+--------------+
| Date       | Hours | Project_Name |
+------------+-------+--------------+
| 2004-10-18 |     4 | Project A    |
| 2004-10-18 |     3 | Project B    |
| 2004-10-19 |     3 | Project A    |
| 2004-10-19 |     5 | Project B    |
| 2004-10-20 |     4 | Project A    |
| 2004-10-20 |     3 | Project B    |
| 2004-10-21 |     1 | Project A    |
| 2004-10-21 |     7 | Project B    |
| 2004-10-22 |     2 | Project A    |
| 2004-10-22 |     6 | Project B    |
+------------+-------+--------------+


All is working fine except I would like the query to show zero values for 
days where there are no entries i.e. the 23rd and 24th in this example. I 
thought that the LEFT JOIN would achieve this but unfortunately not! If 
someone could show me where I am going wrong I would be most grateful.

Many thanks,



Shaun


Thread
SELECTING Non existing Datesshaun thornburgh20 Oct
  • Re: SELECTING Non existing DatesMartijn Tonies20 Oct
  • Re: SELECTING Non existing DatesSGreen20 Oct
  • Re: SELECTING Non existing DatesHarald Fuchs20 Oct
Re: SELECTING Non existing Datesshaun thornburgh21 Oct