Changing start-date in MySQL for week

Question

I found the following code to help in creating a weekly report based on a start date of Friday. The instructions say to replace ".$startWeekDay." with a 4. When I put '".$startDay."' as '2013-01-30', I get errors.

Also I get a report by day rather than week as I desire.

SELECT  SUM(cost) AS total, 
    CONCAT(IF(date - INTERVAL 6 day < '".$startDay."', 
            '".$startDay."', 
            IF(WEEKDAY(date - INTERVAL 6 DAY) = ".$startWeekDay.", 
                date - INTERVAL 6 DAY, 
                date - INTERVAL ((WEEKDAY(date) - ".$startWeekDay.")) DAY)),
        ' - ', date) AS week, 
        IF((WEEKDAY(date) - ".$startWeekDay.") >= 0, 
            TO_DAYS(date) - (WEEKDAY(date) - ".$startWeekDay."), 
            TO_DAYS(date) - (7 - (".$startWeekDay." - WEEKDAY(date))))     AS sortDay 
FROM    daily_expense
WHERE   date BETWEEN '".$startDay."' AND '".$endDay."' 
GROUP BY sortDay;

The following code is what I am using

SELECT count(DISTINCT (
UserID)
) AS total, CONCAT(IF(date(LastModified) - INTERVAL 6 day < date(LastModified), 
            date(LastModified), 
            IF(WEEKDAY(date(LastModified) - INTERVAL 6 DAY) = 4, 
                 date(LastModified) - INTERVAL 6 DAY, 
                date(LastModified) - INTERVAL             ((WEEKDAY(date(LastModified)) - 4)) DAY)),
        ' - ',  date(LastModified)) AS week 

FROM  `Purchase` 
WHERE  `OfferingID` =87
AND  `Status` 
IN ( 1, 4 )

GROUP BY week

The output I get is

total   week
3   2013-01-30 - 2013-01-30
1   2013-01-31 - 2013-01-31

score 1 · Answer 1 · answered Apr 07 '13 at 02:35

I'm not sure exactly how you want to display your week, the sql above is attempting to display date ranges. If this isn't a requirement, your query could be very simple, you can just offset your time by two days (since friday is two days away from the natural star of the week) and use the week function to get the week number.

The query would look like this:

select count(distinct (UserID)) as total
, year( LastModified + interval 2 day ) as year
, week( LastModified + interval 2 day ) as week_number
FROM  `Purchase` 
WHERE  `OfferingID` =87
AND  `Status` 
IN ( 1, 4 )
group by year, week_number;

Changing start-date in MySQL for week

1 Answers1