Help with WEEKOFYEAR - finding all weeks between two dates

jfulton · #1 (**permalink**) 10-04-05, 16:13

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

felixg · #2 (**permalink**) 10-05-05, 04:04

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

Internet Services

Web Development

Digital Marketing

Consumer Tech