Calendar dimension

Data Modeling
Enthusiast

Calendar dimension

Hi,

Does anybody have calendar dimension design and scripts. If it is big, can you please mail to my id ktraj1@gmail.com?

I want to have holiday, weekend,  and more indicators country( if possible) and some field indicators.

Thanks and regards,

Raja

8 REPLIES
Enthusiast

Re: Calendar dimension

Hi Team,

           We are maintaing calendar dimension data as if Teradata maintain calendar data in calendar tables

Issue :- If i take first date of this year "20140101" then week name will be wednesday, and week day will be 4.

Means Wednesday to Tuesday will be my first one week  (Output :-2014 Week01)

But my current requirement is , If any year starts with any particular day, then from that day(dayname) to first sunday,will be first week of the year

Example :- 20140101 starts with wednesday which is nothing but 4(weekday) so my first week should be closed by first sunday only then next week will be started with monday and colsed with Sunday.

                    Simply :- Wednesday to Sunday ---> (2014 Week01)

                    Simply :- Monday to  Sunday      ---> (2014 Week02)

                    Simply :- Monday to  Sunday     ---> (2014 Week03)

in this way i've to update my entire table .

Please find the below mentioned details for same. Thanks in advance
SELECT 
CAST(DAY_DT AS DATE),
DAY_OF_WEEK,
DAY_NAME,
PER_NAME_WEEK
FROM W_DAY_D
WHERE ROW_WID BETWEEN '20140101' AND '20140131' ORDER BY ROW_WID ASC
Date         DayofWeek     DayName         DefOpt            ExpOpt

1/1/2014    4                 Wednesday     2014 Week01 2014 Week01

1/2/2014    5                 Thursday         2014 Week01 2014 Week01

1/3/2014    6                 Friday              2014 Week01 2014 Week01

1/4/2014    7                 Saturday         2014 Week01 2014 Week01

1/5/2014    1                 Sunday           2014 Week01 2014 Week01

1/6/2014    2                 Monday           2014 Week01 2014 Week02

1/7/2014    3                 Tuesday         2014 Week01 2014 Week02

1/8/2014    4                 Wednesday    2014 Week02 2014 Week02

1/9/2014    5                 Thursday        2014 Week02 2014 Week02

1/10/2014  6                 Friday             2014 Week02 2014 Week02

1/11/2014  7                 Saturday        2014 Week02 2014 Week02

1/12/2014  1                 Sunday           2014 Week02 2014 Week02

Enthusiast

Re: Calendar dimension

What version of TD are you on?

Junior Contributor

Re: Calendar dimension

Your current calendar is an Oracle calendar :-)

What about 2013-12-30 and 2013-12-31, should it be 2014 Week 01, too, or 2013 Week 53?

Enthusiast

Re: Calendar dimension


Hi Dieter,

                 Happy to see your mail, Please find the details as you requsted

DAY_DT        DAY_OF_WEEK  DAY_NAME        PER_NAME_WEEK

12/30/2013 2                        Monday            2013 Week52

12/31/2013 3                        Tuesday           2013 Week53

Thanx

Mahesh 

Enthusiast

Re: Calendar dimension

Hi Vande,

     Version is TD 13.10

Thanx

Mahesh

Junior Contributor

Re: Calendar dimension

Hi Mahesh,

assuming that 2013 Week52 was a typo and should be 53, too?

This is based on the built-in sys_calendar, so you can easily adjust it to your calendar:

SELECT 
-- Jan 1st = ((calendar_date / 10000 * 10000) + 101 (DATE))
-- Find the previous monday <= Jan 1st
-- Calculate the number of days between the date and that monday
-- MOD 7 + 1 returns the week number
(calendar_date
- (((calendar_date / 10000 * 10000) + 101 (DATE))
- (((calendar_date / 10000 * 10000) + 101 (DATE)) - DATE '0001-01-01') MOD 7
)
) / 7 +1 AS wk
calendar_date,
day_of_week,
FROM sys_calendar.CALENDAR
Enthusiast

Re: Calendar dimension

Amazing ! No words to praise you sir :)

It's working fine

Thank you so much sir Dieter!

Enthusiast

Re: Calendar dimension

Hi Raja

Can you mail me the design and scripts for creating calendar dimension table please @varanasianup@gmail.com

Thanks in advance !