Member Avatar for acaciasd

Hi,

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

John Henderson

  • 2 Contributors
  • 1 Reply
  • 107 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by guru12
Member Avatar for guru12

Dear friend

Use the BETWEEN query

Like this $sql = "SELECT total FROM mytable WHERE date BETWEEN satartdate value AND end date value"; Thank and Reagrds

Robin

Be a part of the DaniWeb community

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