UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip:
For the specific question at hand, here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the only non-intuitive part of your question, and it's the only tangible information you actually didn't include.
The "best" (read:most efficient) way to populate a calendar table, IMHO, is to use a set, rather than a loop. And you can generate this set without burying logic into user-defined functions, which really don't gain you anything but encapsulation - otherwise it's just another object to maintain. I talk about this in a lot more detail in this blog series:
If you want to keep using your function, make sure it's not a multi-statement table-valued function; that's not going to be efficient at all. You want to make sure that it is inline (e.g. has a single RETURN statement and no explicit @table declaration), has WITH SCHEMABINDING, and doesn't use recursive CTEs. Outside of a function, here is how I would do it:
CREATE TABLE dbo.DateDimension
(
[Date] DATE PRIMARY KEY,
[DayOfWeek_Number] TINYINT,
[DayOfWeek_Name] VARCHAR(9),
[DayOfWeek_ShortName] VARCHAR(3),
[Week_Number] TINYINT,
[Fiscal_DayOfMonth] TINYINT,
[Fiscal_Month_Number] TINYINT,
[Fiscal_Month_Name] VARCHAR(12),
[Fiscal_Month_ShortName] VARCHAR(3),
[Fiscal_Quarter] TINYINT,
[Fiscal_Year] SMALLINT,
[Calendar_DayOfMonth] TINYINT,
[Calendar_Month Number] TINYINT,
[Calendar_Month_Name] VARCHAR(9),
[Calendar_Month_ShortName] VARCHAR(3),
[Calendar_Quarter] TINYINT,
[Calendar_Year] SMALLINT,
[IsLeapYear] BIT,
[IsWeekDay] BIT,
[IsWeekend] BIT,
[IsWorkday] BIT,
[IsHoliday] BIT,
[HolidayName] VARCHAR(255)
);
-- add indexes, constraints, etc.
With the table in place, you can perform a single, set-based insert of as many years of data as you want from whatever start date you choose. Just specify the start date and the number of years. I use a "stacked CTE" technique to avoid redundancy and only perform a whole slew of calculations once; the output columns from the earlier CTEs are then subsequently used in further calculations later on.
-- these are important:
SET LANGUAGE US_ENGLISH;
SET DATEFIRST 7;
DECLARE @start DATE = '20100101', @years TINYINT = 20;
;WITH src AS
(
-- you don't need a function for this...
SELECT TOP (DATEDIFF(DAY, @start, DATEADD(YEAR, @years, @start)))
d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY s1.number)-1, @start)
FROM master.dbo.spt_values AS s1
CROSS JOIN master.dbo.spt_values AS s2
-- your own numbers table works much better here, but this'll do
),
w AS
(
SELECT d,
wd = DATEPART(WEEKDAY,d),
wdname = DATENAME(WEEKDAY,d),
wnum = DATEPART(ISO_WEEK,d),
qnum = DATEPART(QUARTER, d),
y = YEAR(d),
m = MONTH(d),
mname = DATENAME(MONTH,d),
md = DAY(d)
FROM src
),
q AS
(
SELECT *,
wdsname = LEFT(wdname,3),
msname = LEFT(mname,3),
IsWeekday = CASE WHEN wd IN (1,7) THEN 0 ELSE 1 END,
fq1 = DATEADD(DAY,25,DATEADD(MONTH,2,DATEADD(YEAR,YEAR(d)-1900,0)))
FROM w
),
q1 AS
(
SELECT *,
-- useless, just inverse of IsWeekday, but okay:
IsWeekend = CASE WHEN IsWeekday = 1 THEN 0 ELSE 1 END,
fq = COALESCE(NULLIF(DATEDIFF(QUARTER,DATEADD(DAY,6,fq1),d)
+ CASE WHEN md >= 26 AND m%3 = 0 THEN 2 ELSE 1 END,0),4)
FROM q
)
--INSERT dbo.DimWithDateAllPersisted(Date)
SELECT
DateKey = d,
DayOfWeek_Number = wd,
DayOfWeek_Name = wdname,
DayOfWeek_ShortName = wdsname,
Week_Number = wnum,
-- I'll update these four lines when I have usable info
Fiscal_DayOfMonth = 0,--'?magic?',
Fiscal_Month_Number = 0,--'?magic?',
Fiscal_Month_Name = 0,--'?magic?',
Fiscal_Month_ShortName = 0,--'?magic?',
Fiscal_Quarter = fq,
Fiscal_Year = CASE WHEN fq = 4 AND m < 3 THEN y-1 ELSE y END,
Calendar_DayOfMonth = md,
Calendar_Month_Number = m,
Calendar_Month_Name = mname,
Calendar_Month_ShortName = msname,
Calendar_Quarter = qnum,
Calendar_Year = y,
IsLeapYear = CASE
WHEN (y%4 = 0 AND y%100 != 0) OR (y%400 = 0) THEN 1 ELSE 0 END,
IsWeekday,
IsWeekend,
IsWorkday = CASE WHEN IsWeekday = 1 THEN 1 ELSE 0 END,
IsHoliday = 0,
HolidayName = ''
FROM q1;
Now, you still have these "holiday" and "workday" columns left to deal with - this gets a little more cumbersome, but you need to update those three columns with any holidays that appear in your date range. Things like Christmas Day are really easy:
UPDATE dbo.DateDimension
SET IsWorkday = 0, IsHoliday = 1, HolidayName = 'Christmas'
WHERE Calendar_Month_Number = 12 AND Calendar_DayOfMonth = 25;
Things like Easter get a lot trickier - I have blogged some ideas here many years ago.
And of course your company non-workdays that have absolutely nothing to do with public holidays etc. must be updated directly by you - SQL Server isn't going to have some built-in way to know your company's calendar.
Now, I purposely stayed away from computing any of these columns, because you said something like the end users have previously preferred fields they can drag and drop - I'm not sure if the end users really know or care if the source of a column is a real column, a computed column, or comes from a view, query or function...
Assuming you do want to look into computing some of these columns to ease on your maintenance (and persist them to pay storage for query speed), you can look into that. However, just as a warning, some of these columns can't be defined as computed and persisted because they are non-deterministic. Here's one example, and how to get around it.
CREATE TABLE dbo.Test
(
[date] DATE PRIMARY KEY,
DayOfWeek_Number AS DATEPART(WEEKDAY, [date]) PERSISTED
);
Results:
Msg 4936, Level 16, State 1, Line 130
Computed column 'DayOfWeek_Number' in table 'Test' cannot be persisted because the column is non-deterministic.
The reason this can't be persisted is because many date-related functions rely on the session settings of the user, like DATEFIRST. SQL Server can't persist the above column because DATEPART(WEEKDAY should give different results - given the same data - for two different users who happen to have different DATEFIRST settings.
Then you might get clever, and say, well, I can set it to be the number of days, modulo 7, offset from some day I know to be a Saturday (say, '2000-01-01'). So you try:
CREATE TABLE dbo.Test
(
[date] DATE PRIMARY KEY,
DayOfWeek_Number AS
COALESCE(NULLIF(DATEDIFF(DAY,'20000101',[date])%7,0),7) PERSISTED
);
But, same error.
Instead of using an implicit convert from a string literal that represents a date time in an unambiguous (to us, but not SQL Server) format, we can use the number of days between the "zero date" (1900-01-01) and that date we know is a Saturday (2000-01-01). If we use an integer here to represent the difference in days, SQL Server can't complain, because there is no way to misinterpret that number. So this works:
-- SELECT DATEDIFF(DAY, 0, '20000101'); -- 36524
CREATE TABLE dbo.Test
(
[date] DATE PRIMARY KEY,
DayOfWeek_Number AS
COALESCE(NULLIF(DATEDIFF(DAY,36524,[date])%7,0),7) PERSISTED
-----------------------------^^^^^ only change
);
Success!
If you're interested in pursuing computed columns for some of these calculations, let me know.
Oh, and one last thing: I don't know why you would ever scrub down this table and re-populate it from scratch. How many of these things are going to change? Are you going to alter your fiscal year constantly? Change how you want to spell March? Set your week to start on Monday one week and Thursday the next? This really should be a build-it-up-once table, and then you make minor tweaks (like updating individual rows with new/changed holiday information).
