Avoid overlapping months and years

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;
*********************************