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