Re: How can a group by clause be modified so the end weeks of the year won't be split?

On Wed, 28 Jan 2009 12:10:34 -0800 (PST), Ted Byers wrote:

>I have a SELECT statement similar to the following:
>
>SELECT YEAR(transaction_date) AS y,WEEK(transaction_date) AS w, COUNT
>(*) AS c FROM transaction_data_view WHERE mid = 300
> GROUP BY YEAR(transaction_date),WEEK(transaction_date)
> ORDER BY YEAR(transaction_date),WEEK(transaction_date);
>
>The ONLY problem, here, is that if New Years day occurs in the middle
>of the week, that week's data will be split at the first second of the
>New Year. Of course, my real select statement is much more complex
>than this, involving joins of both tables and views (and it is quick),
>but this suffices to make the only remaining problem obvious.
>
>If it matters, this is being done in MySQL.
>
>So, how, then, can I fix the GROUP BY and ORDER BY clauses?

Hi Ted,

I don't know the date calculation functions in MySQL, but a generic approach would be to either
a) calculate number of weeks since a fixed date, or b) calculate number of days since a fixed date and divide by 7 (using integer division, or if MySQL doesn't have that, truncating the result to integer).

The former is shorter and easier to understand, but depends on you and the server agreeing on what day is considered the first day of the week. The latter gives you ultimate control over what day of the week is first, by changing the fixed date in the calculation.

In SQL Server, the code would be

GROUP BY DATEDIFF(week, '20000101', transaction_date) or
GROUP BY DATEDIFF(day, '20000101', transaction_date) / 7

(I chose Jan 1st 2000 at random, I have no idea what day of the week will be the week boundary and what date to use to get saturday, sunday, or monday as first day of the week - some experimenting should help).

Best, Hugo Received on Wed Jan 28 2009 - 22:53:13 CET