SQL to create table with weeks in period - having breaks at month's end (Oracle)

I need to create a table with the following structure:

calendar week; week start date; week end date

which contains all weeks beginning in 2007 until the current week.

The special thing is, that when an end of month falls within a week, the week is cut in two slices - one record 开发者_Python百科that has a start date that is the beginning of the week and the end date is the last day of the month, and one record that contains the dates of the rest of the week (start date is first of the new month, end date is last day of the week).

Example (beginning of week is monday):

calendar week; week start date; week end date; ... 2009 cW48; 23.11.2009; 29.11.2009 --"normal" week with 7 days, beginning monday and ending sunday 2009 cW49; 30.11.2009; 30.11.2009 --first part of the CW49, which ends at last day of the month 2009 cW49; 01.12.2009; 06.12.2009 --second part of the CW49, which begins at fist day of the new month 2009 cW50; 07.12.2009; 13.12.2009 --"normal" week, without a monthly break ...

How to create such a table in Oracle (SQL or PL SQL)?

you could create your table like this:

SQL> CREATE TABLE weeks AS
  2  WITH generator AS (
  3     SELECT DATE '2007-01-01' + LEVEL - 1 dt
  4       FROM dual
  5     CONNECT BY LEVEL <= SYSDATE - DATE '2007-01-01' + 1
  6  )
  7  SELECT to_char(dt, 'YYYY "cW"IW') "calendar week",
  8         dt "week start date",
  9         least(next_day(dt - 1, to_char(DATE '2007-01-07', 'DAY')),
 10               last_day(dt)) "week end date"
 11    FROM generator
 12   WHERE to_char(dt, 'D') = to_char(DATE '2007-01-01', 'D') -- only mondays
 13      OR to_char(dt, 'dd') = 1 --or first day of the month
 14  ;

Table created
SQL> SELECT *
  2    FROM weeks
  3   WHERE "week start date" BETWEEN DATE '2009-11-15' AND DATE '2009-12-15';

calendar week week start date week end date
------------- --------------- -------------
2009 cW47     16/11/2009      22/11/2009
2009 cW48     23/11/2009      29/11/2009
2009 cW49     30/11/2009      30/11/2009
2009 cW49     01/12/2009      06/12/2009

this is a small PL/SQL block to create your table. Change the table names, if you want.

-- create table weeks(year number, week number, b_date date, e_date date);

DECLARE
    i  DATE;
    s  DATE;
    wk NUMBER;
    yr NUMBER;

    FUNCTION getweek(l DATE) RETURN NUMBER IS
    BEGIN
        -- !! week of year, iso standard, (31. dec can be on the first week of next year) !!
        RETURN to_char(l, 'IW');
    END;
BEGIN
    i := to_date('2007-01-01', 'yyyy-mm-dd');
    s := i;

    DELETE weeks;

    WHILE i <= to_date('2009-12-31', 'yyyy-mm-dd') LOOP

        IF trunc(s, 'MONTH') <> trunc(i, 'MONTH') OR
           getweek(s) <> getweek(i) THEN

            wk := getweek(s);
            yr := to_char(s, 'YYYY');
            INSERT INTO weeks VALUES (yr, wk, s, i - 1);

            s := i;
            i := s;
        END IF;

        i := i + 1;
    END LOOP;

    i := i - 1;

    wk := getweek(s);
    yr := to_char(s, 'YYYY');
    INSERT INTO weeks VALUES (yr, wk, s, i);

    COMMIT;
END;

You can create the table this way:

    create table weeks(cw, start_date, end_date)
    as
    select to_char(gen.d,'YYYY "cW"IW')
         , min(gen.d)
         , max(gen.d)
      from (
             select to_date('01.01.2007','DD.MM.YYYY') + level -1 d
               from dual
            connect by level <= 1500 -- approx. number of days
           ) gen
     group by
           to_char(gen.d,'YYYY "cW"IW')
         , to_char(gen.d,'YYYY MM IW')
    having min(gen.d) <= sysdate

The point is in use IW and MM format mask to get number of week and month, and then group results by both of them.