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

Re: create weekly or monthly report 80 80

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

SELECT * FROM mytable group by date_format(mydate,'%U') as week
Re: create weekly or monthly report 80 80

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

Re: create weekly or monthly report 80 80

%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())
Re: create weekly or monthly report 80 80

thanks alot for your help

Re: create weekly or monthly report 80 80

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

Re: create weekly or monthly report 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.