• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

sql server 2000, data for a time dimension table

I'm using SQL Server 2000 on a XP Pro machine.  

I'm working on a DW project and need to populate a time dimension table in the data warehouse. This data is not in the OLTP and I would like to know if anyone knows of a script that will populate the columns in this table like, month, day of week, week, year. I can make the table, that is not a problem. The issue is getting the data and populating the table. Make sense?  

I would rather not have to type all this information into the table.  

Thank you for your help.
0
jandhb
Asked:
jandhb
  • 3
1 Solution
 
arbertCommented:
This is the script we use--should give you a good start (or something to look at):


--
--
DECLARE @Date AS DATETIME

SELECT @Date = '1900-01-01'                  --startdate

WHILE @Date <> '2040-01-05'                 --enddate
Begin
  INSERT INTO HWED_T_DATE (full_date, day_number_in_month,day_name,day_abbreviation,
                  day_of_week, weekday_weekend, week_number_in_year, week_begin_date,
                  month_number, month_name, MONTH_ABBREVIATION, quarter_number,
                  century_year, year_month, last_day_in_month, same_weekday_year_ago)
 values(
      @Date --as FullDate
      , DAY(@Date) --as DayNumberInMonth
      , DATENAME(DW, @Date) --as DayName
      , SUBSTRING(DATENAME(DW, @Date), 1, 3) --as DayAbbreviated
      , DATEPART(DW, @Date) --as DayOfWeek
      , --WeekdayWeekend =
            Case DATEPART(DW, @Date)
                  When 1 Then 'Weekend'
                  When 7 Then 'Weekend'
                  Else 'Weekday'
            End
      , DATENAME(WK, @Date) --as WeekNumberInYear
      , @Date + 1 - DATEPART(DW, @Date)-- as WeekBeginDate
      , MONTH (@Date) --as MonthNumber
      , DATENAME (MM, @Date) --as MonthName
      , SUBSTRING(DATENAME(MM, @Date), 1, 3)-- as MonthAbbreviated
      , 'Q' + DATENAME (Q, @Date)-- as Quarter
      , YEAR(@Date)--as Year
      , Year(@Date) * 100 + Month(@Date)-- as YearMo
      , --LastDayInMonthFlag =
            Case
                  WHEN MONTH(@Date + 1) <> MONTH(@Date) THEN 'Y'
                  Else 'N'
            End
      , @Date - 364 --as SameWeekdayYearAgo
)
Select @Date = @Date + 1
End
0
 
arbertCommented:
jandhb, did this help at all?
0
 
jandhbAuthor Commented:
arbert, thanks for that. i'm working with a team on this project and will have to get back with you soon about what you gave here. I can post back soon regarding this. Thank you for your help.

Just one question though....

1. What does the , symbol represent before many of the lines of code?

thanks.
0
 
arbertCommented:
They're part of the insert statement and are place holders between the columns.....
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now