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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.