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

#1

On Jan 28, 4:57 pm, toby <t... (AT) telegraphics (DOT) com.au> wrote:

Quote:

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.

Try GROUP BY TO_DAYS(transaction_date+x) DIV 7

I mean, GROUP BY (TO_DAYS(transaction_date)+x) DIV 7

Quote:

Where x is a correction to give you the correct week starting day.

If it matters, this is being done in MySQL.

So, how, then, can I fix the GROUP BY and ORDER BY clauses?

Thanks,

Ted

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.theory comp.databases.theory

Re: How can a group by clause be modified so the end weeks of theyear won't be split? - 01-28-2009 , 03:58 PM