Useful way to group data by week to avoid the quandary of weeks overlapping months and years.
CREATE OR REPLACE PROCEDURE weekly_proc IS
CURSOR x_cur IS
SELECT DISTINCT SUBSTR(TO_CHAR(date1),4,3) m, SUBSTR(TO_CHAR(date1),1,2) w, COUNT (*) cnt
FROM cpad_errors
GROUP BY SUBSTR(TO_CHAR(date1),4,3), SUBSTR(TO_CHAR(date1),1,2);
x_rec x_cur%ROWTYPE;
week_var NUMBER;
BEGIN
EXECUTE IMMEDIATE 'truncate table week_test';
OPEN x_cur;
LOOP
FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%notfound;
IF TO_NUMBER(x_rec.w) < 8
THEN week_var := 1;
ELSIF TO_NUMBER(x_rec.w) < 15 AND TO_NUMBER(x_rec.w) > 7
THEN week_var := 2;
ELSIF TO_NUMBER(x_rec.w) < 22 AND TO_NUMBER(x_rec.w) > 16
THEN week_var := 3;
ELSE week_var := 4;
END IF;
INSERT INTO week_test (WEEK_NUM, TTL, MNTH)
VALUES (week_var, x_rec.cnt, x_rec.m);
END LOOP;
CLOSE x_cur;
COMMIT;
END weekly_proc;
*********************************
SELECT mnth|| ' week '|| week_num, SUM(ttl)
FROM week_test
GROUP BY mnth|| ' week '|| week_num;
*********************************
Read more »