All Forums Data Modeling
Raja_KT 1246 posts Joined 07/09
10 Nov 2013
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

Raja K Thaw

My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1

Street Children suffer not by their fault. We can help them if we want.

MaheshJessy 26 posts Joined 12/10
07 Aug 2014

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

 

VandeBergB 182 posts Joined 09/06
07 Aug 2014

What version of TD are you on?

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
07 Aug 2014

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?

Dieter

MaheshJessy 26 posts Joined 12/10
07 Aug 2014

 

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 

MaheshJessy 26 posts Joined 12/10
07 Aug 2014

Hi Vande,
     Version is TD 13.10
Thanx
Mahesh
 
 

dnoeth 4628 posts Joined 11/04
08 Aug 2014

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

 

Dieter

MaheshJessy 26 posts Joined 12/10
08 Aug 2014

Amazing ! No words to praise you sir :)
It's working fine
Thank you so much sir Dieter!
 

varanasianup 14 posts Joined 09/14
03 Mar 2015

Hi Raja
 
Can you mail me the design and scripts for creating calendar dimension table please @varanasianup@gmail.com
Thanks in advance !

You must sign in to leave a comment.