grouping set of dates into weeks

[esport]

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

[hellonoko]

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?

 

 

 

 

 

[esport]

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.

[PFMaBiSmAd]

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

[esport]

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.

[PFMaBiSmAd]

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.

[esport]

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.

[fenway]

Should be able to MOD that rather easily.

[esport]

not sure what you mean? MOD as in returning a remainder?

 

[Barand]

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 |
+---------------------+--------+