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

Thanks one and all, for the ideas.

The following scriptlet defining a couple functions is what I came up with as drop in alternates to WEEK() and YEAR(). Of course, the names could be better, and the arguments could be made more rationale (i.e. to take a DATE rather than a much too long VARCHAR - but this was a quick and dirty first attempt to handle the data that is there using ideas inspired by your responses, which includes fine grained timestamps along with date).

DELIMITER // DROP FUNCTION IF EXISTS `adjWholeWeek`// CREATE FUNCTION `adjWholeWeek`(dt VARCHAR(256)) RETURNS INTEGER BEGIN
  DECLARE theDate DATE DEFAULT DATE(dt);   DECLARE priorSunday DATE DEFAULT DATE_ADD(theDate,INTERVAL -
(DAYOFWEEK(theDate)-1) DAY);

  DECLARE rv INTEGER DEFAULT WEEK(priorSunday);   RETURN rv;
END
//

DROP FUNCTION IF EXISTS `adjWholeYEAR`// CREATE FUNCTION `adjWholeYEAR`(dt VARCHAR(256)) RETURNS INTEGER BEGIN
  DECLARE theDate DATE DEFAULT DATE(dt);   DECLARE priorSunday DATE DEFAULT DATE_ADD(theDate,INTERVAL -
(DAYOFWEEK(theDate)-1) DAY);

  DECLARE rv INTEGER DEFAULT YEAR(priorSunday);   RETURN rv;
END
//

DELIMITER ; I figure creating functions like these, once validated and refined, will provide a solution for all time, avoiding maintenance issues that would be associated with calendar tables. This code is to be used in risk reports generated automatically (in a cron job or scheduled task) and I don't want to have to worry about that failing, or giving bad output, because someone forgot to update a calendar table for a coming year in time.

I did not opt for the tables in part because this situation is simpler than the situation described in relation to tables. This business operates 24-7-52" i.e. no holidays, ever. I think, if I had to worry about holidays, given this is a global business, I'd do the date manipulation in Perl (which has a number of marvelous packages for handling them) because I'd then be facing a nightmare of time zones interacting with a huge variety of national and religious holidays as they'd exist across the planet. Thank God this business knows nothing of holidays (and the data is entirely stored in UTC)!!!! Timezones complicate things enough as it is (at present, we use them only for displaying data in certain reports sent to clients).

Thanks again

Ted Received on Thu Jan 29 2009 - 18:00:42 CET