Hi, I am having a problem with using dates grouped by week where it spans over 2 months. I would like the query to return all hours worked by week number, however week 48 is causing problems because it spans 2 months - actually I am presuming this is the problem!
mysql> SELECT DATE_FORMAT(T.Date, '%x-%v-%m') AS 'YearWeek', T.Status, U.User_ID, U.User_Firstname, U.User_Lastname, SUM(T.Hours) AS Total_Hours FROM Users U, Timesheets T WHERE T.User_ID = U.User_ID AND U.User_ID = 1 GROUP BY YearWeek, T.User_ID ORDER BY YearWeek DESC; +------------+------------+---------+----------------+---------------+-------------+ | YearWeek | Status | User_ID | User_Firstname | User_Lastname | Total_Hours | +------------+------------+---------+----------------+---------------+-------------+ | 2005-48-12 | Open | 1 | Shaun | x | 14.75 | | 2005-48-11 | Open | 1 | Shaun | x | 23.5 | | 2005-47-11 | Unapproved | 1 | Shaun | x | 20.75 | +------------+------------+---------+----------------+---------------+-------------+ 3 rows in set (0.00 sec) mysql> DESC Timesheets; +-------------------+-------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+------------+----------------+ | Timesheet_ID | int(11) | | PRI | NULL | auto_increment | | Type | varchar(40) | YES | | NULL | | | Project_ID | int(11) | YES | | NULL | | | Unavailability_ID | int(11) | YES | | NULL | | | User_ID | int(11) | | | 0 | | | Hours | float | | | 0 | | | Date | date | | | 0000-00-00 | | | Status | varchar(40) | YES | | Open | | +-------------------+-------------+------+-----+------------+----------------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM Timesheets WHERE User_ID = 1; +--------------+---------+------------+-------------------+---------+-------+------------+------------+ | Timesheet_ID | Type | Project_ID | Unavailability_ID | User_ID | Hours | Date | Status | +--------------+---------+------------+-------------------+---------+-------+------------+------------+ | 9 | Project | 71 | NULL | 1 | 8.25 | 2005-11-25 | Unapproved | | 10 | Project | 71 | NULL | 1 | 4.5 | 2005-11-26 | Unapproved | | 11 | Project | 66 | NULL | 1 | 8 | 2005-11-24 | Unapproved | | 54 | Project | 66 | NULL | 1 | 4.75 | 2005-11-28 | Open | | 53 | Project | 67 | NULL | 1 | 7.5 | 2005-12-02 | Open | | 52 | Project | 67 | NULL | 1 | 7.25 | 2005-12-01 | Open | | 51 | Project | 67 | NULL | 1 | 10 | 2005-11-30 | Open | | 50 | Project | 67 | NULL | 1 | 6.25 | 2005-11-29 | Open | | 55 | Project | 66 | NULL | 1 | 2.5 | 2005-11-29 | Open | +--------------+---------+------------+-------------------+---------+-------+------------+------------+ 9 rows in set (0.00 sec) mysql> Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
