I wonder if there is a solution to my query; I need to be able to produce a weekly report that will display all daily totals for that week or any previous week. I have toyed around with the weekday() function and the Week() function, but I don't know mysql well enough to produce the required results.
Here is my sql statement...
SELECT *, sum(payments.payment) as totpay1, sum(payments.payment2) as totpay2, sum(payments.payment+payments.payment2) as totalcash, operative, week(stampoperative), weekday(stampoperative) FROM customers, payments WHERE weekday(stampoperative) ='1' and WEEK(stampoperative) = WEEK(now()) and customers.customerid = payments.customerid and paid='1' and code<> 'No Access' and code<>'DPP'
What I thought I could do was use the week() function to find the week number and then use the wekday() to pull all records for that day.
So as an example I could have week 13 and search for day 1, being a Monday or day 2 being a Tuesday etc, etc. It would also be helpful to be able to display the actual date for week 13 day 1 too.
Any guidance would be very much appreciated
Thanks in advance