"Shaun" <[EMAIL PROTECTED]> wrote on 12/02/2005 10:09:44 AM:
> 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? > > You are correct, this function: DATE_FORMAT(T.Date, '%x-%v-%m') is returning a second value for 2005-48 exactly because it spans two months. I think if you just simplified your format string to '%x-%v' you will get the behavior you want. Here is how I would rewrite it: SELECT DATE_FORMAT(T.Date, '%x-%v') AS 'YearWeek' , GROUP_CONCAT(month(t.Date)) as 'Month(s)' , T.Status , U.User_ID , U.User_Firstname , U.User_Lastname , SUM(T.Hours) AS Total_Hours FROM Users U INNER JOIN Timesheets T ON T.User_ID = U.User_ID AND U.User_ID = 1 GROUP BY YearWeek, T.User_ID ORDER BY YearWeek DESC; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
