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

#1

"toby" <toby (AT) telegraphics (DOT) com.au> wrote

Quote:

On Jan 28, 4:57 pm, toby <t... (AT) telegraphics (DOT) com.au> wrote:
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

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

I once had a similar problem. The company's calendar was full of quirks.
The company's fiscal year always began on the Sunday after the 4 of July.
Every year was divided into 4 fiscal quarters. Every fiscal quarter was
divided into 4, 5, or even 6 fiscal weeks. Every fiscal week was divided
into 7 days. Every day was divided into 3 shifts. People wanted queries
cut by the ordinary calendar, but they also wanted queries cut by the
company calendar. We also needed to know which days were company holidays,
and some other stuff.

The solution was to build a table that we called ALMANAC. The primary key
was the date and the shift. (I don't remember thecolumn names, but DATE was
a reserved word). This table contained one row per shift. It contained
every conceivable attribute of the shift or of the date, like which fiscal
week (out of 53) the date belongs to, or whether the date was a company
holiday. It also contained attributes that mimicked the built in date
functions, like MONTH(d). Even at 3 rows to a day, the table took about
1000 rows for every year. We then wrote a quickie little program to load
ALMANAC with all the relevant data.

OK, OK, so this table wasn't normalized. So sue me. OK, OK, this table
materialized some results that could "more efficiently" be computed by built
in functions like WEEKDAY. So sue me.

The result was that all those crazy calendar calculations were embedded in
just one program, namely the program that loaded the ALMANAC. The rest of
the system treated ALMANAC like it was just ordinary data. You'd be amazed
at how much this simplifies life. We could generate a report sliced by
month, and in another fifteen minutes specify the same report sliced by
quarter, or year, or fiscal week, or whatever.

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 the year won't be split?

comp.databases.theory comp.databases.theory

Re: How can a group by clause be modified so the end weeks of the year won't be split? - 01-29-2009 , 07:39 AM