esport Posted October 21, 2008 Share Posted October 21, 2008 Hi, I am having trouble trying to figure out how to group a set of dates in weeks and calculate the hours for that week. I have a table called timesheets that have a user_id, timesheet_date and hrs fields. I am trying to produce a report with a given start date and an end date that will display the results in week ending and the week endings are Fridays. Below is an example of the report i want to produce. user_idweek endingtotal hrs 110-10-200848 117-10-200842 124-10-200848 210-10-200848 217-10-200842 224-10-200848 Any assistance in how to go about it will be very much appreciated. Thanks in advance Daniel Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/ Share on other sites More sharing options...
hellonoko Posted October 21, 2008 Share Posted October 21, 2008 You could use the date() function or getDate() function to see if a certain date was a friday and then SELECT the 7 rows before that. Or he 7 rows ahead. Or all rows until the date equaled your current date + or - 7. Something like that? Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-670704 Share on other sites More sharing options...
esport Posted October 22, 2008 Author Share Posted October 22, 2008 thanks for the reply. still puzzled on how I am going to achieve this. I was thinking of getting all the data within the dates and then run some php code with a loop grouping the weeks and counting the hours. Im sure i can do this in one query. Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-671468 Share on other sites More sharing options...
PFMaBiSmAd Posted October 22, 2008 Share Posted October 22, 2008 mysql has a YEARWEEK() function that returns the year/week of a date - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_yearweek You would use this in a SELECT statement to produce the year/week number for each row, then use this value in a GROUP BY statement to give you groups of rows by the year/week they are in, use a SUM() in the SELECT to get mysql to add up the totals in each group. Untested but should work - SELECT YEARWEEK(your_date_column) AS yrwk, SUM(worked_hours_column) AS total FROM your_table WHERE your_where_clause_here GROUP BY yrkw Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-671487 Share on other sites More sharing options...
esport Posted October 22, 2008 Author Share Posted October 22, 2008 Thanks for your reply. Getting somewhere with this. How does it know what the end of the week is. In my situation, the weekending day is Friday. Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-671522 Share on other sites More sharing options...
PFMaBiSmAd Posted October 22, 2008 Share Posted October 22, 2008 Please read the information in the manual at the link I posted, that is the only way to effectively program. It mentions the mode parameter and where in that same page to find what the various values mean. Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-671572 Share on other sites More sharing options...
esport Posted October 31, 2008 Author Share Posted October 31, 2008 Thanks. I had a look at that link but the mode only refers to having the week either start on on a Sunday or a Monday. However, I'm trying to make it so the start of the week is Saturday and the end of the week is Friday. Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-679035 Share on other sites More sharing options...
fenway Posted October 31, 2008 Share Posted October 31, 2008 Should be able to MOD that rather easily. Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-679434 Share on other sites More sharing options...
esport Posted October 31, 2008 Author Share Posted October 31, 2008 not sure what you mean? MOD as in returning a remainder? Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-679691 Share on other sites More sharing options...
Barand Posted November 1, 2008 Share Posted November 1, 2008 You could use the offset in days from a known first saturday date E.G. SELECT tdate, TRUNCATE((TO_DAYS(tdate)-TO_DAYS('2008-09-06'))/7, 0) as weekno FROM data ORDER BY tdate --> +---------------------+--------+ | tdate | weekno | +---------------------+--------+ | 2008-09-06 00:00:00 | 0 | | 2008-09-06 00:00:00 | 0 | | 2008-09-07 00:00:00 | 0 | | 2008-09-08 00:00:00 | 0 | | 2008-09-08 00:00:00 | 0 | | 2008-09-09 00:00:00 | 0 | | 2008-09-10 00:00:00 | 0 | | 2008-09-10 00:00:00 | 0 | | 2008-09-11 00:00:00 | 0 | | 2008-09-12 00:00:00 | 0 | | 2008-09-14 00:00:00 | 1 | | 2008-09-14 00:00:00 | 1 | | 2008-09-16 00:00:00 | 1 | | 2008-09-17 00:00:00 | 1 | | 2008-09-20 00:00:00 | 2 | | 2008-09-22 00:00:00 | 2 | | 2008-09-23 00:00:00 | 2 | | 2008-09-23 00:00:00 | 2 | | 2008-09-24 00:00:00 | 2 | | 2008-09-25 00:00:00 | 2 | | 2008-09-28 00:00:00 | 3 | | 2008-09-30 00:00:00 | 3 | | 2008-10-02 00:00:00 | 3 | | 2008-10-02 00:00:00 | 3 | | 2008-10-04 00:00:00 | 4 | | 2008-10-04 00:00:00 | 4 | | 2008-10-06 00:00:00 | 4 | | 2008-10-06 00:00:00 | 4 | | 2008-10-09 00:00:00 | 4 | | 2008-10-10 00:00:00 | 4 | | 2008-10-12 00:00:00 | 5 | | 2008-10-12 00:00:00 | 5 | | 2008-10-19 00:00:00 | 6 | | 2008-10-20 00:00:00 | 6 | | 2008-10-21 00:00:00 | 6 | | 2008-10-22 00:00:00 | 6 | | 2008-10-23 00:00:00 | 6 | | 2008-10-23 00:00:00 | 6 | | 2008-10-25 00:00:00 | 7 | | 2008-10-27 00:00:00 | 7 | | 2008-10-27 00:00:00 | 7 | | 2008-10-27 00:00:00 | 7 | | 2008-10-29 00:00:00 | 7 | | 2008-10-29 00:00:00 | 7 | | 2008-10-30 00:00:00 | 7 | | 2008-10-31 00:00:00 | 7 | | 2008-10-31 00:00:00 | 7 | | 2008-10-31 00:00:00 | 7 | | 2008-10-31 00:00:00 | 7 | | 2008-11-01 00:00:00 | 8 | +---------------------+--------+ Link to comment https://forums.phpfreaks.com/topic/129335-grouping-set-of-dates-into-weeks/#findComment-679904 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.