Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297

    Question Unanswered: Help with WEEKOFYEAR - finding all weeks between two dates

    OK, this may sound a little confusing so I'm going to try to be as clear as possible. (This is for a php web-app.)

    I have a query where I am summing the totals of inventory by week of year. This works fine when there is inventory for the week. However, at the beginning of the month, there will be no inventory for the end of the month so no results will be returned.

    What I want I guess, is a union of (the weeks of the year and inventory within a given month) on (the weeks of the year within a given month without inventory).

    Here is the current sql query, which sums inventory grouped by week of year within a given month. (minDate = 1st of month, maxDate = last of month)

    Code:
    $sql = "	SELECT SUM(i.pallets_actual) AS pallets
    			, WEEKOFYEAR(l.date_sent) AS week
    		FROM inventory i
    			,loads l
    		  	,load_inventory li
    	     	WHERE l.date_sent >= '" . mysql_datetime($minDate) . "'
    			AND l.date_sent < '" . mysql_datetime($maxDate) . "'  
    			AND l.loadid = li.loadid
    			AND li.inventoryid = i.inventoryid
    			AND i.active = 'T'
    		GROUP BY WEEKOFYEAR(l.date_sent)
    		ORDER BY WEEKOFYEAR(l.date_sent)
    I mean, I guess I could just get the week of the year for the minDate and maxDate and then "fill in the blanks" with php, but I'd rather have it all returned in one sql query as one result set. Any help??

    Thanks.
    -Jesse

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by jfulton
    I mean, I guess I could just get the week of the year for the minDate and maxDate and then "fill in the blanks" with php, but I'd rather have it all returned in one sql query as one result set.
    One way in to create a calendar table:
    Code:
    CREATE TABLE calendar (
        dt DATE NOT NULL PRIMARY KEY,
        yr INT NOT NULL,
        wk INT NOT NULL,
        KEY (yr, wk),
        KEY (wk)
    );
    
    INSERT INTO calendar (dt, yr, wk) VALUES
    ('2005-01-01', 2004, 53),
    ('2005-01-02', 2004, 53),
    ('2005-01-03', 2005,  1),
    ('2005-01-04', 2005,  1),
    ...
    ('2005-12-30', 2005, 52),
    ('2005-12-31', 2005, 52);
    and use it as driving table in your SELECT query.

    --
    felix

Posting Permissions

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