Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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