List:General Discussion« Previous MessageNext Message »
From:shaun thornburgh Date:October 21 2004 9:55am
Subject:Re: SELECTING Non existing Dates
View as plain text  
Hi,

Thanks for your reply but the result isnt quite right!!! :

mysql> SELECT T.Date, T.Hours, P.Project_Name FROM Timesheets T
    -> LEFT JOIN Projects P ON (P.Project_ID = T.Project_ID and T.Date >=
    -> '2004-10-18'
    -> AND T.Date <= '2004-10-24')
    -> WHERE T.User_ID = "1"
    -> AND T.Type = "Project";
+------------+-------+--------------+
| Date       | Hours | Project_Name |
+------------+-------+--------------+
| 2004-10-18 |     4 | Project A    |
| 2004-10-19 |     3 | Project A    |
| 2004-10-20 |     4 | Project A    |
| 2004-10-21 |     1 | Project A    |
| 2004-10-22 |     2 | Project A    |
| 2004-10-18 |     3 | Project B    |
| 2004-10-19 |     5 | Project B    |
| 2004-10-20 |     3 | Project B    |
| 2004-10-21 |     7 | Project B    |
| 2004-10-22 |     6 | Project B    |
+------------+-------+--------------+
10 rows in set (0.00 sec)

mysql>


Any ideas?



>From: "Martijn Tonies" <m.tonies@stripped>
>To: <mysql@stripped>
>Subject: Re: SELECTING Non existing Dates
>Date: Wed, 20 Oct 2004 15:10:51 +0200
>
>Hello Shaun,
>
> > 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
>
>Try:
>
>LEFT JOIN Projects P ON (P.Project_ID = T.Project_ID and T.Date >=
>'2004-10-18'
>  AND T.Date <= '2004-10-24')
>WHERE T.User_ID = "1"
>  AND T.Type = "Project"
>
>
> > 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    |
> > +------------+-------+--------------+
>
>With regards,
>
>Martijn Tonies
>Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
>Server.
>Upscene Productions
>http://www.upscene.com
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    
>http://lists.mysql.com/mysql?unsub=1
>


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