September 22, 2008 at 1:40 am
Hi folks,
I am trying to create a time dimension table on my database.This table will consist all dates beginning from 2005 to 2010.
Firstly I have created a user defined function named GetDays as follows:
----------------------
CREATE FUNCTION GetDays(@Start datetime, @Stop datetime)
RETURNS @Result TABLE (Date datetime PRIMARY KEY)
AS BEGIN
WHILE @Start<=@Stop BEGIN
INSERT INTO @Result VALUES (@Start)
SET @Start=@Start+1
END
RETURN
END
and then I will populate this table by using this function as follows:
-------------
set datefirst 1
SELECT date as FullDateAlternateKey,
(convert(int,floor( convert(float,date))))%7+1 as DayNumberOfWeek,
DAtename(weekday,date) AS 'Day Name',
datepart(day,date) as DayNumberOfMonth,
DAtename(month,date) AS 'EnglishMonthName',
datepart(dy,date) as DayNumberOfYear,
datepart(year,date) as CalendarYear,
CalendarSemester=
CASE
WHEN CAST(datepart(month,date) as int) > 6 then 2
else
1
end
FROM GetDays('20081220','20090102')
----------------------
At the and of the year DayNumberOfWeek column values contiunes from the previous year.But this valuse should be reset for a new year.Right??
So how can I fix this issue?? have any idea?
Note:
I have attached a JPEG file.You can see the resultset at this screenshot.
Regards
September 23, 2008 at 5:14 am
No, DayNumberOfWeek should NOT be reset when the year changes, because it is just that - Day Number Of Week - and not Day Number Of Year.
Second, have a look at the Tally Tables article by Jeff Moden, it will help you achieve your target much faster.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply