MySQL / PHP - Get results by week even if midweek?

duckduckgoose

I've been playing with this for a while now and can't seem to figure it out... Basically, I'm trying to return results for the first week of the month, but only for the 1st day through whatever date is the last day of the first week. So, if the 1st lands on a Tuesday, I just want to pull results from the 1st through the 5th, but if it's a full week, then, it should return the week from Sun to Mon. And, if it's the last week, and the week ends on a Wednesday, it should only pull Sunday Through Wednesday... I've been using the query below, but it seems to pull the full 7 days regardless...

SELECT 
FORMAT(SUM(IF(WEEK(sm.creation_date) = WEEK('2009-09-01'), bv.mtd_budgeted_sales, 0)), 2) AS a1WTD, 
FORMAT(SUM(IF(WEEK(sm.creation_date) = WEEK('2009-09-01'), bv.mtd_budget_covers, 0)), 2) AS a2WTD,
sm.summary_id
FROM base_values bv LEFT JOIN summaries sm ON bv.summary_id = sm.summary_id
WHERE sm.r_id = 6 AND WEEK(sm.creation_date) = WEEK('2009-09-01') GROUP BY sm.r_id;

johanafm

$year = 2009;
$month = 9;
$day = 1;

$week = date('W', mktime(0, 0, 0, $month, $day, $year));

for ($lastDay = $day, $checkWeek = $week; $checkWeek == $week; ++$lastDay)
	$checkWeek = date('W', mktime(0, 0, 0, $month, $lastDay, $year));

--$lastDay;

$from = $year .'-' . $month . '-' . $day;
$to = mktime('Y-m-d', mktime($month, $lastDay, $year));


$sql = "... WHERE sm.r_id = 6 AND sm.creation_date BETWEEN $from AND $to";