Re: Aggregate by calandar weeks?
Ok, for those of you who have OnLine or SE version 5.0 and higher, here is
the most elegant and best solution:
Write a stored procedure (week(date)) and use it as a function. Example:
SELECT week(mydate), sum(col1), avg(col2) ...
FROM my_table
GROUP BY 1
And here is the stored procedure:
CREATE PROCEDURE week (date_in DATE) RETURNING CHAR(5);
-- This sp accepts a date and returns the year and the number of the week
-- in the form YY-WW, example 93-35
-- Don't ask me about the algorithm, all I know it works. :-)
DEFINE utar, utvika CHAR(2);
DEFINE i,j,k SMALLINT;
DEFINE d1, d2 DATE;
LET k = YEAR(date_in);
LET d1 = MDY(1,1,k);
LET i = YEAR(date_in) - 1900;
WHILE WEEKDAY(d1) != 1
LET d1 = d1 - 1 UNITS DAY;
END WHILE;
LET j = date_in - d1;
LET k = j / 7 + 1;
IF k > 52 THEN
LET i = i + 1;
LET j = 1;
ELSE
LET j = k;
END IF;
LET utar = i;
LET utvika = j;
IF LENGTH(utvika) = 1 THEN
LET utvika = "0"||utvika;
END IF
RETURN utar||"-"||utvika;
END PROCEDURE;
-Snorri
--
Snorri Bergmann | Internet: snorri@strengur.is
Strengur Consulting Engineers | Bang: ...!uunet!strengur.is!snorri
Tryggvagata 26 | Phone: +354 1 624700
101 Reykjavik Iceland | Telefax: +354 1 625785