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).
Best Answer
Using your
CASE
expression example, here's how it could be written:After you accepted my original answer, I noticed where other posters were advocating the use of PIVOT to achieve the same result as multiple
CASE
expressions. For completeness of my answer, I decided to try my hand at usingPIVOT
to provide additional options for you (and learn something myself in the process). I've found the information from Questions About Pivoting Data in SQL Server You Were Too Shy to Ask to be quite useful when trying to understand how to usePIVOT
. You'll learn a lot aboutPIVOT
by working through the examples (static and dynamicPIVOT
) in the link - I know I did.To summarize some of the key points of PIVOT (from the link):
I'm by no means a
PIVOT
expert and other posters may have better solutions (hey, I'm willing to learn a better way), but here's my take on it.I'm including two examples of
PIVOT
- one static and one dynamic.(Hopefully, by using my examples and the information in the link provided, you'll understand what I'm doing)
The first example is a static
PIVOT
and is similar to theCASE
expression solution I originally provided (except it usesPIVOT
).Notice that my sample data now includes years 'outside' of the
CASE
expression. That information won't show up in the static version, but you'll see how we can solve that in the dynamic version later.Static PIVOT
Did you notice the data outside the CASE expressions didn't show up?
Let's try to solve that by using a dynamic
PIVOT
.Dynamic PIVOT
(Due to using dynamic SQL, we have to switch to using a temporary table instead of a table variable)
Your question prompted me to research and learn more about
PIVOT
- thanks.