create weekly or monthly report

[mrhankey]

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

[smantscheff]

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

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

[mrhankey]

thanks for the reply. what does the %U do to the date?

looked at this but could not see the %U?

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

i take it if i wanted the dates to match the current week would i just put:

WHERE week = NOW()

thanks again

[smantscheff]

%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())

[mrhankey]

thanks alot for your help

[mrhankey]

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

[mrhankey]

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