create date dimension

Hi ,
i have  chart tables which i want to put in database charts looks like below:
charts year start 1996 till 2019 looking at charts it look like they increment weekcode by 1 for every week only if it start sunday.

January 1996
 
SUN M T W  TH FR SAT      weekcode
 
    1 2  3  4  5  6      
 7  8 9  10 11 12 13              0968
14 15 16 17 18 19 20           0969
21 22 23 24 25 26 27           0970
28 29 30 31                           0971
 
 
january 2006
 
SUN M T W  TH FR SAT weekcode
 
1 2  3  4  5  6  7                1489
8 9  10 11 12 13 14        1490
15 16 17 18 19 20 21     1491
22 23 24 25 26 27 28     1492
29 30 31                          1493
 
 
 
 
want to create a table
 
D_DATE like below:
 
Weekcode  Year           Week
0986              1996        1
0969              1996       2
0970              1996       3
0971              1996      4
 
so on
 
1489             2006       1
1490            2006        2
1491           2006        3
1492           2006        4
1493           2006        5
 
and
so on till 2019
sam2929Asked:
Who is Participating?
 
sdstuberCommented:
SELECT TO_CHAR(weekcode, 'fm0000') weekcode,
       EXTRACT(YEAR FROM sunday) year,
       ROW_NUMBER() OVER(PARTITION BY TRUNC(sunday, 'yyy') ORDER BY sunday) week,
       sunday
  FROM (    SELECT DATE '1996-01-07' + 7 * (LEVEL - 1) sunday, 968 + LEVEL - 1 weekcode
              FROM DUAL
        CONNECT BY DATE '1996-01-01' + 7 * (LEVEL - 1) +7< DATE '2020-01-01')

I included the sunday column so you could check the dates, you can remove it from your table if you don't want it, but I think I would leave it in
0
 
ste5anSenior DeveloperCommented:
You're on the right trak, but I would use a full calendar table, cause its always handy to have one at hands. Something like

Date, DateNumber (YYYYMMDD), DayName, WeekNumber, WeekCode, QuarterNumber, DayNumber, MonthNumber, YearNumber, IsWeekend.

And maybe fiscal year information.
0
 
sdstuberCommented:
You can generate your monthly charts similarly

  SELECT month,
         sun,
         m,
         t,
         w,
         th,
         fr,
         sat,
         TO_CHAR(weekcode, 'fm0000') weekcode
    FROM (SELECT TO_CHAR(dt, 'fmMonth YYYY') month,
                 dt,
                 TO_CHAR(dt, 'd') day_of_week,
                 weekcode
            FROM (    SELECT DATE '1995-12-31' + (LEVEL - 1) dt, 967 + FLOOR((LEVEL - 1) / 7) weekcode
                        FROM DUAL
                  CONNECT BY DATE '1995-12-31' + (LEVEL - 1) < DATE '2020-01-01')
           WHERE dt >= DATE '1996-01-01')
         PIVOT (MAX(TO_CHAR(dt, 'fmdd')) FOR day_of_week IN ('1' sun, '2' m, '3' t, '4' w, '5' th, '6' fr, '7' sat))
ORDER BY TO_DATE(month, 'Month YYYY'), weekcode;
0
 
Geert GOracle dbaCommented:
save the date as correct as possible, preferably up to the second in your table
you can derive months, weeks, etc from a date like that, not vice versa
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.