Re: sql and week starting ending numbers (dates)

SELECT FROM_DAYS(TO_DAYS(purchaseDate)-DAYOFYEAR(purchaseDate)+WEEK(purchaseDate)*7) will hopefully return the date you need. You'll have to enclose all that in a DATE_FORMAT to get a printable date tho. Last minute note: you might have to subtract 1 from the last term because I guess MySQL starts counting with week 1. HTH Bogdan Adam Royle wrote:

Hello all, I am creating a finance manager in php and mysql for my own accounting purposes (and to broaden my skills in php and sql) and I have a couple of pages where I view statistics on how much I spend, etc, and what patterns are formed from my spending. I have made a couple of pages with different statistics, and on one specific page, I show how much I spend total each week... The following query does that, but I want to display the start date and end date of the week. $sql = "SELECT YEARWEEK(purchaseDate,1) AS week,

            CONCAT_WS(' ',MONTHNAME(purchaseDate), YEAR(purchaseDate),'- Wk', WEEK(purchaseDate,1)) AS month,
            SUM(itemPrice) AS total
        FROM tblCashflow
        WHERE userID_FK = ${user['userID']}
        AND Type = 'Expenditure'
        GROUP BY week";

OK, so the sql works, and the output on the page looks like something below...

February 2002 - Wk 5    $49.70
February 2002 - Wk 7    $12.95
February 2002 - Wk 8    $53.29
February 2002 - Wk 9    $275.99
March 2002 - Wk 10    $78.95
March 2002 - Wk 11    $171.15
March 2002 - Wk 12    $82.85
March 2002 - Wk 13    $20.50
April 2002 - Wk 14    $55.15
April 2002 - Wk 15    $30.55
April 2002 - Wk 16    $104.20
April 2002 - Wk 17    $461.95
May 2002 - Wk 18    $152.75
May 2002 - Wk 19    $219.80
May 2002 - Wk 20    $133.55
May 2002 - Wk 21    $98.65

Now thats all well and good, but I want to make it display something like the following:

February 1 - 7 2002     $49.70
February 8 - 15 2002     $12.95

February 16 - 23 2002 $53.29 etc etc etc Does anyone have any idea on how this could be achieved? I looked at the mySQL time and Date functions, but I couldn't find out how to do it with that, and I think doing the work in PHP might be the answer.... Also, currently the SQL statement only shows the weeks where I have spent money, so if I don't spend anything in that week, it won't show up at all, although I would like it to say "January 12 - 19 2002 $0" or something like that. Below is my table schema for the above query Adam CREATE TABLE tblCashflow ( ID bigint(20) NOT NULL auto_increment, purchaseDate date default NULL, categoryID_FK tinyint(4) default NULL, itemName text, itemPrice float default '0', isEssential tinyint(4) default '0', paymentTypeID_FK tinyint(4) default '1', receiverID_FK tinyint(4) default NULL, itemDesc text, Type text, userID_FK int(11) default NULL, PRIMARY KEY (ID) );