0

hi,

i have a site which i want to generate a weekly report of activity from. the monthly report should be fine as i could set the query to format the date into month and where "date" is NOW() however how can i generate a weekly report in mysql?

any tips would be great.

many thanks

2
Contributors
6
Replies
8
Views
6 Years
Discussion Span
Last Post by mrhankey
0

Use the "week" modificators for grouping on your date field.

SELECT * FROM mytable group by date_format(mydate,'%U') as week
1

%U is a formatting parameter for the date_format function which displays the week of the input date.
To match only the current week use

WHERE WEEK(somedatefield) = week(now())
0

hi,

i have tried this:

SELECT commission.ProductID, commission.Product, commission.ClientID, commission.ClientFirstName, commission.ClientLastName, commission.UserID, commission.Firstname, commission.Lastname, commission.Amount, commission.NPW, commission.DatePaid
FROM commission
GROUP BY DATE_FORMAT(commission.DatePaid,'%U') as WEEK

but it errors, can you see what i have done wrong?

thanks again

0

what i am trying to acheive is a weekly report league table. i have a view that shows everything paid, but i would like to be able to view task activity also in this report. what woudl be the best way to query the commission table to show the top user for that week with what they have been paid?

many thanks

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.