CoderZone.org

Category: >> Oracle PL/SQL >> Avoid overlapping months and years Bookmark and Share

<< lastnext >>

Snippet Name: Avoid overlapping months and years

Description: Useful way to group data by week to avoid the quandary of weeks overlapping months and years.

Also see:
» FUNCTIONS: date/time
» UPDATE: Update a partitioned table
» UPDATE: Update based on a record
» UPDATE: Update Object Table
» UPDATE: with RETURNING clause
» UPDATE: Nested Table Update exampl...
» UPDATE: Correlated Update
» UPDATE: Update from a SELECT state...
» UPDATE: based on multiple returned...
» UPDATE: Update based on a query
» UPDATE: Update multiple rows
» UPDATE: update a specific record
» UPDATE: Single row
» Date Functions: EXTRACT
» Date Functions: TO_DSINTERVAL
» Date Functions: TO_NCHAR
» Date Functions: TO_YMINTERVAL
» Date Functions: Calculate elapsed ...
» Date/Time Calculations: Get second...
» Date: Find first day of the month
» Date Calculations
» Date Functions: WHERE Clause Joins
» Date Functions: TRUNC
» Date Functions: SYSDATE
» Date Functions: TO_CHAR
» Date Functions: ROUND
» Date Functions: NEXT_DAY
» Date Functions: NEW_TIME
» Date Functions: MONTHS_BETWEEN
» Date Functions: MIN

Comment: (none)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 12th, 2009

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;
*********************************
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 7