-- PL/SQL code to create week days (1,2,...7) in each -- week in a typical four week (28 days) month. SET SERVEROUTPUT ON SIZE 1000000; DECLARE v_max SIMPLE_INTEGER := 28; v_day SIMPLE_INTEGER := 0; BEGIN FOR idx IN 1..v_max LOOP v_day := v_day +1; DBMS_OUTPUT.PUT_LINE(' Month Day '||LPAD(idx,2,'0')|| ' Week '||CEIL(idx/7)|| ' Week Day '||v_day); IF MOD(idx,7) = 0 THEN v_day := 0; END IF; END LOOP; END; / => Output of above PL/SQL code -- Query to create week days (1,2,...7) using analytic -- function -- ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) -- Same week day data can be created by using the -- analytic function -- RANK() OVER (PARTITION BY ... ORDER BY ...) -- or by -- COUNT(*) OVER (PARTITION BY ... ORDER BY ...) SELECT level day, CEIL(level/7) week, ROW_NUMBER() OVER -- or RANK() OVER (PARTITION BY CEIL(level/7) ORDER BY level) week_day FROM DUAL CONNECT BY level < 29; => Output of above Analytic Function Row_Number() SELECT level day, CEIL(level/7) week, COUNT(*) OVER (PARTITION BY CEIL(level/7) ORDER BY level) week_day FROM DUAL CONNECT BY level < 29; => Output of above Analytic Function Count(*) Over()
56086