Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    18

    Question Unanswered: This weeks invoiced qty

    I am trying to find out the qty of items invoiced this week.

    I can get the last 7 days with the following query:

    SELECT SUM(quantity) from invoices WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= inv_date

    My problem is that on Monday morning I need the qty to start at 0 again but the above will include part of last week.

    Can anyone help with this?

    Thanks...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on what front end you are using you may be able to do what you want more esaily.

    But in MySQL there are several date functions that may help
    dayofWeek() will return todays day Sunday=1, Saturday=7
    weekday()
    date_add()
    date_sub()
    -if you are using version 5 then your could write sproc to handle it otherwise you will have to write a fairly convluted SQL statement
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Sounds like you want to group by the week of the year?

    Check out the date and time functions. You may want to look at WEEK() and WEEKOFYEAR().

  4. #4
    Join Date
    Feb 2004
    Posts
    18
    Darn it, I always forget to post the version number that I am using

    Thanks for the pointers. I think I may have a solution in mind, I just have to play with it a little

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bayder
    ...Darn it, I always forget to post the version number that I am using ...
    if thats a problem just create a signature admitting to what versions of what you are using - it removes some of the doubt on trying to help.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •