 |
|
8000XXXX Errors ASP.NET 2.0 Classic ASP 1.0
Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary calendar table?
A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree.
Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:
A common practice is to have a calendar table pre-populated with some or all of the information we need, enabling us to accomplish most programming tasks with a much simpler set-based solution. The following is an example of a table that could be used in a production environment, though it has all the bells and whistles—you may not need to populate all of the columns I define here.
Now, let's populate it with data. Let's assume we want 30 years of data, from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:
However, this will take a significant amount of time. A better way, if you create a numbers table starting at 1 (as described in Article #2516), is to run this query without the looping:
Which executes almost instantly. Now, I went ahead beforehand and determined that 10,957 was the number of days I was interested in (I did not just pick that number out of the sky). You can experiment a bit with the following query, in order to determine what your highest value of n should be (just change the start and end dates):
Yes, this all seems a little complex, but keep in mind that this is going to reduce your after-the-fact workload and programming logic significantly.
And yes, I understand that the time it takes to generate the numbers table overrides the time you save in generating the calendar table. However, I included the logic here for two reasons:
(I describe all of these columns in the chart below.)
Before that, I'd like to add a column to account for UTC offset calculations, which many of us have to deal with (on the East Coast of the U.S., for example, we set the clocks forward one hour on the first Sunday of April, and set them back one hour on the last Sunday in October).
The easiest way I've found to do this is run through a cursor. Sounds painful, but you will only have to do it once, and it beats checking every single date in the range to determine whether it falls inside or outside the range. Keep in mind that this specific portion of the script is geared to the Eastern time zone in the U.S.; you may need to make adjustments to the @offset values, as well as the logic checks, depending on what part of the world you live in (and whether or not you care about UTCOffset at all).
Now that the data is populated (for the most part), let's talk about each column we've created, what it means, and how it can be used. Remember that not every column is required: the example I'm giving here is really a superset of options you can use, but you can certainly feel free to only use a subset for your particular needs.
Note that I didn't include any indexes, other than the one that is created by default on the dt column. You will need to experiment with the types of queries you are running to see where indexes make the most sense.
Week Number
Many, many, many people are dissatisfied with the way SQL Server calculates week number. Its strange algorithm used in DATEPART(WEEK, <date>) does not always yield the same number as the ISO standard, which dictates that week 1 is the first week with 4 days. To deal with that, we can borrow from Books Online and create the following function:
Then we can update the table:
Simple, huh?
Holidays
Next you will need to make sure you update your calendar table appropriately with your company's holidays. I included a brief example in the chart above, but you will have to repeat for all of your company's holidays. Some holidays are a little more difficult to calculate than others, because they do not fall on the same day every year. Here are some sample scripts, thanks to John Gilson and Michael C#. First, I added a HolidayDescription column so that when you review the holidays in the table, it will be less cryptic than just a date:
Now we can focus on updating the data. Notice that some of the calculations are quite straight forward, but others are more difficult.
I'll leave it as an exercise to the reader to figure out when the Monday or the Friday should be updated to reflect a holiday... this often depends on your company's rules. Some holidays, like Presidents' Day, are up to the states, so that's another case you'll have to account for individually. And finally, I left Easter until last, because us crazy Catholics come up with so many rules to make our lives miserable! :-)
To handle Easter, first I created this function, which returns Easter Sunday for any given year:
Then I created two functions to get the real days you get off work (Good Friday and Easter Monday) based on that single calculation. (Might be a slight performance hit, but it makes sense to keep that logic in one single piece of code. If you only have 30 or 50 years in your table, it won't be noticeable; besides, this is only something you have to do when the calendar table is created, not in real time.)
Now, we can easily update the table to apply the Easter holidays. Obviously, if your company only observes the Monday, then don't run the script for Friday. :-)
Okay, I have a table; now what???
All right, that's all great and everything, but how can we use this information to help solve real problems?
Getting a date range as a set
If someone asked you to list all the days in January 2004, you would probably write a quick script in VBScript or C# that iterated through the days manually. Or, you would rhyme them off on your fingers. With a calendar table, you can bring back these dates (and any extraneous information we've stored) as a contiguous set, without any looping.
To make this even easier, you can create a table-valued function in SQL Server 2000:
Now, you can obtain this range simply by calling:
You can create different functions that take different arguments, for example if you are frequently requesting a month at a time (e.g. for a calendar application), you can create a table-valued function or stored procedure that accepts month and year as inputs.
Getting recurring events across months
Here's one that would require complex looping, conditional logic, and maintaining state between loop iterations. Let's say your club meets on the third Friday of every month, and the president wants a quick printout of the dates of all the meetings in 2004. Do you even want to think about that in a loop in T-SQL or the language of your choice? Are you going to get out your Dilbert calendar and jot them down? Of course not. With a calendar table, you can do this in a query with a subquery:
Another thing you might want is to get information about the first day of each month, in the past year. This is a simple query against the calendar table:
Finally, what if we want all the Mondays in the second quarter of 2004? Even if you have a standard fiscal year, this calculation would normally involve looping through all of the days and applying logic that determines if the day is a Monday, and what constitutes being in the second quarter. You would also have to store those results in a #temp table during the loop if you wanted to return the set as a single resultset. With a calendar table, this is so much easier:
Getting the number of business days between two dates
This question comes up quite often, and was the catalyst for this article. Many people would consider writing a UDF that loops through all the days in a range, determines whether each one is a working day, apply business logic to determine if the day is considered a holiday, and keep a running count. Luckily, we can do this much more easily against a calendar table. Let's say we wanted all the business days in April 2004:
An added bonus of the set-based solution is that you can easily change the SELECT list to obtain the actual set of days:
For more treatment on this topic, see Article #2453.
Getting all dates, even those not represented
A calendar table makes it easy to return a full set of dates from a query, regardless of whether or not those dates exist in the actual data. We can use an outer join against the calendar table instead of using a cursor to loop through all of the relevant dates. For example, let's say we wanted a sales breakdown for every day in April 2004, when we only have certain dates populated:
Pre-determine delivery dates
You can easily determine scheduled dates in advance, for example, the arrival date for a car, or the delivery time of a piece of mail. Let's say you know that it takes 9 business days for a particular process; well, we can ask the database what the estimated date is based on how long we assume it takes. Without a calendar table, we'd have to loop from today until some future date, and hope that as we loop through the days and apply logic to determine if the day counts toward delivery, that we don't reach the end and run out of days. :-)
Getting missing dates in overlapping date ranges
This is a problem that happens often. You've got insurance policies, or car rental contracts, and you want to find out which days are not covered. But some of the days overlap. How do you do it? With a resultset as small as this sample, it is easy to determine visually which days are not covered for each contract. With a larger resultset, however, you'd have to get out your cursors and/or a legal pad and a very sharp pencil, if it weren't for the calendar table. Let's assume this sample data:
So, the information the pointy-haired boss is looking for is the following result, since he asked for all dates after the first date of a contract and before the last day that weren't covered:
Even with a calendar table, this wasn't the most intuitive solution, but it is much faster than any cursor-based scheme you will work out.
When you're done:
Calculating 'days to go' for recurring events
This example uses a Birthdays table to determine, given a current date, how many days to go before each person's birthday.
So, we have three birthdays, and we want to determine (given a specific date, usually today) how many days before we hit their birthdays. So we create this stored procedure:
Now, the results when running this stored procedure:
Note that if the birthday has already passed this year, they will not be included in the resultset.
Hopefully I have demonstrated that there are many uses for a calendar table, and even if you think that 36,000 or 72,000 rows are "way more" than you need to solve a particular problem, it will end up saving your butt several times over in the long run. Remember than you should only populate the calendar table with the years of data you need. If you only need to go 10 years out, that's less than 4,000 rows—which should yield acceptable performance even on the most beat-up systems.
The only date-related topic I *don't* recommend a calendar table for is birthdays, because (a) leap years cause funny things to happen, and (b) there are already decent solutions out there (for example, see Article #2233). It can be done with a calendar table, but I'll leave that as an exercise for the reader.
If you have any comments, questions or suggestions about the use of a calendar table, let us know.
Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:
- How many business days between x and y?
- What are all of the dates between the second Tuesday of March and the first Friday in April?
- On what date should I expect this shipment to arrive?
- What were the dates of all the Fridays in this quarter?
- When will the next 13th fall on a Friday?
- What is the UTC offset for all of the days in the first week of April, 2002?
- What is the list of the third Tuesday of every month this year?
A common practice is to have a calendar table pre-populated with some or all of the information we need, enabling us to accomplish most programming tasks with a much simpler set-based solution. The following is an example of a table that could be used in a production environment, though it has all the bells and whistles—you may not need to populate all of the columns I define here.
| CREATE TABLE dbo.Calendar ( dt SMALLDATETIME NOT NULL PRIMARY KEY CLUSTERED, isWeekday BIT, isHoliday BIT, Y SMALLINT, FY SMALLINT, Q TINYINT, M TINYINT, D TINYINT, DW TINYINT, monthname VARCHAR(9), dayname VARCHAR(9), W TINYINT ) GO |
Now, let's populate it with data. Let's assume we want 30 years of data, from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:
| SET NOCOUNT ON DECLARE @dt SMALLDATETIME SET @dt = '20000101' WHILE @dt < '20300101' BEGIN INSERT dbo.Calendar(dt) SELECT @dt SET @dt = @dt + 1 END |
However, this will take a significant amount of time. A better way, if you create a numbers table starting at 1 (as described in Article #2516), is to run this query without the looping:
| INSERT Calendar(dt) SELECT DATEADD(DAY, Number, '20000101') FROM dbo.Numbers WHERE Number <= 10957 ORDER BY Number |
Which executes almost instantly. Now, I went ahead beforehand and determined that 10,957 was the number of days I was interested in (I did not just pick that number out of the sky). You can experiment a bit with the following query, in order to determine what your highest value of n should be (just change the start and end dates):
| SELECT DATEDIFF(DAY, '20000101', '20291231') |
Yes, this all seems a little complex, but keep in mind that this is going to reduce your after-the-fact workload and programming logic significantly.
And yes, I understand that the time it takes to generate the numbers table overrides the time you save in generating the calendar table. However, I included the logic here for two reasons:
- you may already have a numbers table that you can use; and,
- you will be able to use the numbers table for other purporses, I promise (see the article mentioned).
| UPDATE dbo.Calendar SET isWeekday = CASE WHEN DATEPART(DW, dt) IN (1,7) THEN 0 ELSE 1 END, isHoliday = 0, Y = YEAR(dt), FY = YEAR(dt), /* -- if our fiscal year -- starts on May 1st: FY = CASE WHEN MONTH(dt) < 5 THEN YEAR(dt)-1 ELSE YEAR(dt) END, */ Q = CASE WHEN MONTH(dt) <= 3 THEN 1 WHEN MONTH(dt) <= 6 THEN 2 WHEN MONTH(dt) <= 9 THEN 3 ELSE 4 END, M = MONTH(dt), D = DAY(dt), DW = DATEPART(DW, dt), monthname = DATENAME(MONTH, dt), dayname = DATENAME(DW, dt), W = DATEPART(WK, dt) |
(I describe all of these columns in the chart below.)
Before that, I'd like to add a column to account for UTC offset calculations, which many of us have to deal with (on the East Coast of the U.S., for example, we set the clocks forward one hour on the first Sunday of April, and set them back one hour on the last Sunday in October).
| ALTER TABLE dbo.Calendar ADD UTCOffset TINYINT NULL GO |
The easiest way I've found to do this is run through a cursor. Sounds painful, but you will only have to do it once, and it beats checking every single date in the range to determine whether it falls inside or outside the range. Keep in mind that this specific portion of the script is geared to the Eastern time zone in the U.S.; you may need to make adjustments to the @offset values, as well as the logic checks, depending on what part of the world you live in (and whether or not you care about UTCOffset at all).
| SET NOCOUNT ON DECLARE @dt SMALLDATETIME DECLARE @offset TINYINT SET @offset = 5 DECLARE c CURSOR LOCAL STATIC READ_ONLY FOR SELECT dt FROM dbo.Calendar ORDER BY dt OPEN c FETCH NEXT FROM c INTO @dt WHILE @@FETCH_STATUS = 0 BEGIN IF DATENAME(dw, @dt)='Sunday' AND DATEPART(DAY, @dt) <= 7 AND DATENAME(MONTH, @dt) = 'April' SET @offset = 4 IF DATENAME(dw, @dt)='Sunday' AND DATEPART(DAY, @dt) >= 25 AND DATENAME(MONTH, @dt) = 'October' SET @offset = 5 UPDATE dbo.Calendar SET UTCOffset = @offset WHERE dt = @dt FETCH NEXT FROM c INTO @dt END CLOSE c DEALLOCATE c GO |
Now that the data is populated (for the most part), let's talk about each column we've created, what it means, and how it can be used. Remember that not every column is required: the example I'm giving here is really a superset of options you can use, but you can certainly feel free to only use a subset for your particular needs.
| Column Name | Notes |
|---|---|
| dt | This is our primary key, and represents each day we care about in SMALLDATETIME format. The time component is always midnight so we don't have to worry about encompassing an entire day as a range. This will make for efficient JOINs, and will allow us to use BETWEEN (which I typically recommend against: see Article #2280). It will also allow us to query or an exact date (WHERE dt = '20040101') instead of encapsulating all the points of time in a day into a range query. |
| isWeekday | This is a simple column that lets us know whether or not the specific date falls on a weekday. If you have a non-standard DATEFIRST setting, and/or you observe a different working week, experiment with the values in the IN clause (which currently represent Saturday and Sunday, respectively). |
| isHoliday | This column will be populated manually, because each workplace and organization has a different set of days throughout the year which they deem to be holidays or otherwise non-working days. The easy ones will be Christmas, New Years', etc. which have fixed calendar dates. The rest will be tougher, and I'll deal with those below. Your standard updates might look like this, e.g. for Christmas: UPDATE dbo.Calendar SET isHoliday=1 WHERE M = 12 AND D = 25 |
| Y | This is simply a column telling us the year that the date falls in. This can be less cumbersome than manually calling the YEAR() function from within a query, particularly if you have to get the same information multiple times. |
| FY | This column will tell us which fiscal year the date falls in, since not every company out there observes a fiscal year that spans from January 1st to December 31st. You will have to change the script if you observe a different fiscal year (and the script shows an example). |
| Q | This tells us which quarter the date falls in. Like FY, if your fiscal year is non-standard, your quarters will need to be determined differently also, which means you will need to adjust the UPDATE script accordingly. Having this value easily accessible in the table will be much easier than calculating it in your query at runtime, regardless of whether or not you have a standard fiscal year. |
| M | This column gives us the numerical representation of the month (January = 1, February = 2, etc). We can use this, for example, when we're looking for a cross-section of all events that have happened in March, regardless of year. |
| D | This column gives us the numerical representation of the day of the month. This can be useful, for example, if we're curious about how many speeding tickets are issued on the 30th of each month throughout the year. |
| DW | This column gives us the numerical representation of the day of the week (Sunday = 1, Monday = 2, etc). Like isWeekday, you will want to make adjustments if your DATEFIRST setting does NOT consider Sunday to be the first day of the week. |
| monthname | This column gives us the familiar name of the month (January, February, etc). We can use this to make "friendly" queries that search for monthname='October' instead of remembering that October is the 10th month. |
| dayname | This column gives us the familiar name of the day (Sunday, Monday, etc). We can use this to make "friendly" queries that search for dayname='Friday' instead of remembering that Friday is the 6th day of the week. |
| W | This column gives us the week number in the year that the date falls in. So, 2004-01-09 is the second week of 2004. Later I will show how to use the real ISO week number as opposed to SQL Server's back asswards way of calculating the week number. |
| UTCOffset | This column has already received attention, but I included it here for completeness. This will be a number representing the offset from UTC which, in many parts of the world, can differ throughout the year. If your application needs it, it will be much easier to determine the UTCOffset this way, than by checking each date within your stored procedure code. |
Note that I didn't include any indexes, other than the one that is created by default on the dt column. You will need to experiment with the types of queries you are running to see where indexes make the most sense.
Week Number
Many, many, many people are dissatisfied with the way SQL Server calculates week number. Its strange algorithm used in DATEPART(WEEK, <date>) does not always yield the same number as the ISO standard, which dictates that week 1 is the first week with 4 days. To deal with that, we can borrow from Books Online and create the following function:
| CREATE FUNCTION dbo.ISOWeek ( @dt SMALLDATETIME ) RETURNS TINYINT AS BEGIN DECLARE @ISOweek TINYINT SET @ISOweek = DATEPART(WEEK,@dt)+1 -DATEPART(WEEK,RTRIM(YEAR(@dt))+'0104') IF @ISOweek = 0 BEGIN SET @ISOweek = dbo.ISOweek ( RTRIM(YEAR(@dt)-1)+'12'+RTRIM(24+DAY(@dt)) ) + 1 END IF MONTH(@dt) = 12 AND DAY(@dt)-DATEPART(DW,@dt) >= 28 BEGIN SET @ISOweek=1 END RETURN(@ISOweek) END GO |
Then we can update the table:
| UPDATE Calendar SET W = dbo.ISOWeek(dt) |
Simple, huh?
Holidays
Next you will need to make sure you update your calendar table appropriately with your company's holidays. I included a brief example in the chart above, but you will have to repeat for all of your company's holidays. Some holidays are a little more difficult to calculate than others, because they do not fall on the same day every year. Here are some sample scripts, thanks to John Gilson and Michael C#. First, I added a HolidayDescription column so that when you review the holidays in the table, it will be less cryptic than just a date:
| ALTER TABLE Calendar ADD HolidayDescription VARCHAR(32) |
Now we can focus on updating the data. Notice that some of the calculations are quite straight forward, but others are more difficult.
| -- New Year's Day - easy UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'New Years'' Day' WHERE M = 1 AND D = 1 -- Memorial Day - last Monday in May UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Memorial Day' FROM Calendar c1 WHERE M = 5 AND DW = 2 AND NOT EXISTS (SELECT 1 FROM Calendar c2 WHERE M = 5 AND DW = 2 AND c2.Y = c1.Y AND c2.dt > c1.dt) -- Labor Day - first Monday in September UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Labor Day' FROM Calendar c1 WHERE M = 9 AND DW = 2 AND NOT EXISTS (SELECT 1 FROM Calendar c2 WHERE M = 9 AND DW = 2 AND c2.Y = c1.Y AND c2.dt < c1.dt) -- Thanksgiving Thursday - 4th Thursday in November UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Thanksgiving Thursday' FROM Calendar c1 WHERE M = 11 AND DW = 5 AND (SELECT COUNT(*) FROM Calendar c2 WHERE M = 11 AND DW = 5 AND c2.Y = c1.Y AND c2.dt < c1.dt) = 3 -- Traditionally, Thanksgiving Friday, as well -- as long as you haven't pre-configured any -- other Thursday in November to be isHoliday UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Thanksgiving Friday' FROM Calendar c1 WHERE M = 11 AND DW = 6 AND EXISTS (SELECT 1 FROM Calendar c2 WHERE M = 11 AND DW = 5 AND c2.dt = (c1.dt - 1) AND c2.Y = c1.Y AND isHoliday = 1) -- Veterans' Day - easy -- however do this AFTER Thanksgiving calculation -- in case it happens to fall on a Thursday UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Veterans'' Day' WHERE M = 11 AND D = 11 -- Christmas Day - easy UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Christmas Day' WHERE M = 12 AND D = 25 |
I'll leave it as an exercise to the reader to figure out when the Monday or the Friday should be updated to reflect a holiday... this often depends on your company's rules. Some holidays, like Presidents' Day, are up to the states, so that's another case you'll have to account for individually. And finally, I left Easter until last, because us crazy Catholics come up with so many rules to make our lives miserable! :-)
To handle Easter, first I created this function, which returns Easter Sunday for any given year:
| CREATE FUNCTION dbo.GetEasterSunday ( @Y INT ) RETURNS SMALLDATETIME AS BEGIN DECLARE @EpactCalc INT, @PaschalDaysCalc INT, @NumOfDaysToSunday INT, @EasterMonth INT, @EasterDay INT SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30 SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) SET @NumOfDaysToSunday = @PaschalDaysCalc - ( (@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7 ) SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44 SET @EasterDay = @NumOfDaysToSunday + 28 - ( 31 * (@EasterMonth / 4) ) RETURN ( SELECT CONVERT ( SMALLDATETIME, RTRIM(@Y) + RIGHT('0'+RTRIM(@EasterMonth), 2) + RIGHT('0'+RTRIM(@EasterDay), 2) ) ) END GO |
Then I created two functions to get the real days you get off work (Good Friday and Easter Monday) based on that single calculation. (Might be a slight performance hit, but it makes sense to keep that logic in one single piece of code. If you only have 30 or 50 years in your table, it won't be noticeable; besides, this is only something you have to do when the calendar table is created, not in real time.)
| CREATE FUNCTION dbo.GetEasterMonday ( @Y INT ) RETURNS SMALLDATETIME AS BEGIN RETURN (SELECT dbo.GetEasterSunday(@Y) + 1) END GO CREATE FUNCTION dbo.GetGoodFriday ( @Y INT ) RETURNS SMALLDATETIME AS BEGIN RETURN (SELECT dbo.GetEasterSunday(@Y) - 2) END GO |
Now, we can easily update the table to apply the Easter holidays. Obviously, if your company only observes the Monday, then don't run the script for Friday. :-)
| UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Good Friday' WHERE dt = dbo.GetGoodFriday(Y) UPDATE Calendar SET isHoliday = 1, HolidayDescription = 'Easter Monday' WHERE dt = dbo.GetEasterMonday(Y) |
Okay, I have a table; now what???
All right, that's all great and everything, but how can we use this information to help solve real problems?
Getting a date range as a set
If someone asked you to list all the days in January 2004, you would probably write a quick script in VBScript or C# that iterated through the days manually. Or, you would rhyme them off on your fingers. With a calendar table, you can bring back these dates (and any extraneous information we've stored) as a contiguous set, without any looping.
| SELECT dt FROM dbo.Calendar WHERE dt >= '20040101' AND dt < '20040201' ORDER BY dt -- or SELECT dt FROM dbo.Calendar WHERE M = 1 AND Y = 2004 ORDER BY dt |
To make this even easier, you can create a table-valued function in SQL Server 2000:
| CREATE FUNCTION dbo.dateRange ( @sDate SMALLDATETIME, @eDate SMALLDATETIME ) RETURNS TABLE AS RETURN (SELECT dt FROM dbo.Calendar WHERE dt BETWEEN @sDate AND @eDate) GO |
Now, you can obtain this range simply by calling:
| SELECT dt FROM dbo.dateRange('20040101', '20040131') |
You can create different functions that take different arguments, for example if you are frequently requesting a month at a time (e.g. for a calendar application), you can create a table-valued function or stored procedure that accepts month and year as inputs.
Getting recurring events across months
Here's one that would require complex looping, conditional logic, and maintaining state between loop iterations. Let's say your club meets on the third Friday of every month, and the president wants a quick printout of the dates of all the meetings in 2004. Do you even want to think about that in a loop in T-SQL or the language of your choice? Are you going to get out your Dilbert calendar and jot them down? Of course not. With a calendar table, you can do this in a query with a subquery:
| SELECT c.dt FROM dbo.Calendar c WHERE c.dayname = 'Friday' AND c.Y = 2004 AND 3 = (SELECT COUNT(*) FROM dbo.Calendar c2 WHERE c.dt >= c2.dt AND c.Y = c2.Y AND c.M = c2.M AND c.dayname = c2.dayname AND c2.Y = 2004 ) ORDER BY c.dt |
Another thing you might want is to get information about the first day of each month, in the past year. This is a simple query against the calendar table:
| SELECT Y,M,D,dayname FROM dbo.Calendar WHERE D = 1 AND dt >= DATEADD(YEAR, -1, GETDATE()) AND dt < GETDATE() |
Finally, what if we want all the Mondays in the second quarter of 2004? Even if you have a standard fiscal year, this calculation would normally involve looping through all of the days and applying logic that determines if the day is a Monday, and what constitutes being in the second quarter. You would also have to store those results in a #temp table during the loop if you wanted to return the set as a single resultset. With a calendar table, this is so much easier:
| SELECT dt FROM dbo.Calendar WHERE dayname='Monday' AND Q = 2 AND FY = 2004 ORDER BY dt |
Getting the number of business days between two dates
This question comes up quite often, and was the catalyst for this article. Many people would consider writing a UDF that loops through all the days in a range, determines whether each one is a working day, apply business logic to determine if the day is considered a holiday, and keep a running count. Luckily, we can do this much more easily against a calendar table. Let's say we wanted all the business days in April 2004:
| SELECT COUNT(*) FROM dbo.Calendar WHERE isWeekday = 1 AND isHoliday = 0 AND dt >= '20040401' AND dt < '20040501' |
An added bonus of the set-based solution is that you can easily change the SELECT list to obtain the actual set of days:
| SELECT dt,dayname FROM dbo.Calendar WHERE isWeekday = 1 AND isHoliday = 0 AND dt >= '20040401' AND dt < '20040501' ORDER BY dt |
For more treatment on this topic, see Article #2453.
Getting all dates, even those not represented
A calendar table makes it easy to return a full set of dates from a query, regardless of whether or not those dates exist in the actual data. We can use an outer join against the calendar table instead of using a cursor to loop through all of the relevant dates. For example, let's say we wanted a sales breakdown for every day in April 2004, when we only have certain dates populated:
| CREATE TABLE dbo.Sales ( dt SMALLDATETIME, amt DECIMAL(9,2) ) GO SET NOCOUNT ON INSERT dbo.Sales SELECT '20040401', 3000 INSERT dbo.Sales SELECT '20040402', 5000 INSERT dbo.Sales SELECT '20040405', 4000 INSERT dbo.Sales SELECT '20040406', 2000 INSERT dbo.Sales SELECT '20040407', 6000 INSERT dbo.Sales SELECT '20040408', 5000 INSERT dbo.Sales SELECT '20040413', 3000 INSERT dbo.Sales SELECT '20040417', 4000 SELECT c.dt, COALESCE(s.amt, 0.0) FROM dbo.Calendar c LEFT OUTER JOIN dbo.Sales s ON c.dt = s.dt WHERE c.Y = 2004 AND c.monthname = 'April' ORDER BY c.dt GO -- when you're done: DROP TABLE dbo.Sales |
Pre-determine delivery dates
You can easily determine scheduled dates in advance, for example, the arrival date for a car, or the delivery time of a piece of mail. Let's say you know that it takes 9 business days for a particular process; well, we can ask the database what the estimated date is based on how long we assume it takes. Without a calendar table, we'd have to loop from today until some future date, and hope that as we loop through the days and apply logic to determine if the day counts toward delivery, that we don't reach the end and run out of days. :-)
| SELECT c.dt FROM dbo.Calendar c WHERE c.isWeekday = 1 AND c.isHoliday =0 AND 9 = ( SELECT COUNT(*) FROM dbo.Calendar c2 WHERE c2.dt >= GETDATE() AND c2.dt <= c.dt AND c2.isWeekday=1 AND c2.isHoliday=0 ) |
Getting missing dates in overlapping date ranges
This is a problem that happens often. You've got insurance policies, or car rental contracts, and you want to find out which days are not covered. But some of the days overlap. How do you do it? With a resultset as small as this sample, it is easy to determine visually which days are not covered for each contract. With a larger resultset, however, you'd have to get out your cursors and/or a legal pad and a very sharp pencil, if it weren't for the calendar table. Let's assume this sample data:
| CREATE TABLE dbo.Contracts ( ContractID INT, dtStart SMALLDATETIME, dtEnd SMALLDATETIME ) GO SET NOCOUNT ON INSERT dbo.Contracts SELECT 1, '20040301', '20040315' INSERT dbo.Contracts SELECT 1, '20040313', '20040327' INSERT dbo.Contracts SELECT 1, '20040329', '20040406' INSERT dbo.Contracts SELECT 2, '20040303', '20040312' INSERT dbo.Contracts SELECT 2, '20040317', '20040327' INSERT dbo.Contracts SELECT 2, '20040321', '20040405' GO |
So, the information the pointy-haired boss is looking for is the following result, since he asked for all dates after the first date of a contract and before the last day that weren't covered:
| ContractID dt ---------- ---------- 1 2004-03-28 2 2004-03-13 2 2004-03-14 2 2004-03-15 2 2004-03-16 |
Even with a calendar table, this wasn't the most intuitive solution, but it is much faster than any cursor-based scheme you will work out.
| SELECT c1.ContractID, c1.dt FROM ( -- this subquery gets all dates within the MIN -- and MAX for each contract, including those -- NOT covered. SELECT cons.ContractID, cal.dt FROM dbo.Calendar cal INNER JOIN ( SELECT ContractID, dtStart = MIN(dtStart), dtEnd = MAX(dtEnd) FROM Contracts GROUP BY ContractID ) cons ON cal.dt BETWEEN cons.dtStart AND cons.dtEnd ) c1 LEFT OUTER JOIN ( -- this subquery gets all the dates within the -- range for each contract, but leaves out those -- dates not covered (hence the LEFT JOIN) SELECT cons2.ContractID, cal2.dt FROM dbo.Calendar cal2 INNER JOIN dbo.Contracts cons2 ON cal2.dt BETWEEN cons2.dtStart AND cons2.dtEnd ) c2 ON c1.ContractID = c2.ContractID AND c1.dt = c2.dt WHERE c2.dt IS NULL |
When you're done:
| DROP TABLE dbo.Contracts |
Calculating 'days to go' for recurring events
This example uses a Birthdays table to determine, given a current date, how many days to go before each person's birthday.
| CREATE TABLE dbo.Birthdays ( FirstName VARCHAR(10), Birthdate SMALLDATETIME ) GO SET NOCOUNT ON INSERT Birthdays SELECT 'John', '19540102' INSERT Birthdays SELECT 'Sue', '19780301' INSERT Birthdays SELECT 'Bobby', '20011231' |
So, we have three birthdays, and we want to determine (given a specific date, usually today) how many days before we hit their birthdays. So we create this stored procedure:
| CREATE PROCEDURE dbo.getDaysToGo ( @dt SMALLDATETIME = NULL ) AS BEGIN SET NOCOUNT ON SET @dt = COALESCE(@dt, CONVERT(CHAR(8), GETDATE(), 112)) SELECT b.FirstName, DaysToGo = COUNT(*) FROM dbo.Calendar c LEFT OUTER JOIN dbo.Birthdays b ON c.dt > @dt AND c.dt <= DATEADD(YEAR, YEAR(@dt)-YEAR(b.Birthdate), b.Birthdate) WHERE b.FirstName IS NOT NULL GROUP BY b.FirstName ORDER BY b.FirstName END GO |
Now, the results when running this stored procedure:
| EXEC dbo.getDaysToGo '20050101' -- non-leap year EXEC dbo.getDaysToGo '20080101' -- leap year EXEC dbo.getDaysToGo -- executed 20040530 ---------------------------------------- -- results: FirstName DaysToGo --------- -------- Bobby 364 John 1 Sue 59 FirstName DaysToGo --------- -------- Bobby 365 John 1 Sue 60 FirstName DaysToGo --------- -------- Bobby 215 |
Note that if the birthday has already passed this year, they will not be included in the resultset.
Hopefully I have demonstrated that there are many uses for a calendar table, and even if you think that 36,000 or 72,000 rows are "way more" than you need to solve a particular problem, it will end up saving your butt several times over in the long run. Remember than you should only populate the calendar table with the years of data you need. If you only need to go 10 years out, that's less than 4,000 rows—which should yield acceptable performance even on the most beat-up systems.
The only date-related topic I *don't* recommend a calendar table for is birthdays, because (a) leap years cause funny things to happen, and (b) there are already decent solutions out there (for example, see Article #2233). It can be done with a calendar table, but I'll leave that as an exercise for the reader.
If you have any comments, questions or suggestions about the use of a calendar table, let us know.
Related Articles
Are there tools available for auditing changes to SQL Server data? Can I create an index on a BIT column? Can I have optional parameters to my stored procedures? Can I implement an input mask in SQL Server? Can I make SQL Server format dates and times for me? Can I start IDENTITY values at a new seed? Can SQL Server tell me which row was inserted most recently? How can I learn more about undocumented SQL Server stored procedures? How can I make my SQL queries case sensitive? How do I audit changes to SQL Server data? How do I connect to a non-default instance of SQL Server? How do I connect to SQL Server on a port other than 1433? How do I create a cross-tab (or "pivot") query? How do I determine if a table exists in a SQL Server database? How do I drop a SQL Server database? How do I find all the available SQL Servers on my network? How do I get a list of SQL Server tables and their row counts? How do I get rid of Named Pipes / DBNMPNTW errors? How do I get the correct date/time from the msdb.sysjob* tables? How do I get the nth row in a SQL Server table? How do I get the result of dynamic SQL into a variable? How do I handle REPLACE() within an NTEXT column in SQL Server? How do I hide system tables in SQL Server's Enterprise Manager? How do I know which version of SQL Server I'm running? How do I limit the number of rows returned in my resultset? How do I load text or csv file data into SQL Server? How do I manage changes in SQL Server objects? How do I monitor SQL Server performance? How do I prevent linked server errors? How do I reclaim space in SQL Server? How do I recover data from SQL Server's log files? How do I search for special characters (e.g. %) in SQL Server? How do I start SQL Server Agent from ASP? How do I time my T-SQL code? How do I upsize from Access to SQL Server? How do I upsize my MSDE database to full-blown SQL Server 2000? How do I use a variable in a TOP clause in SQL Server? How do I use GETDATE() within a User-Defined Function (UDF)? How should I store an IP address in SQL Server? Schema: How do I find all the foreign keys in a database? SQL Server & MSDE What are reserved Access, ODBC and SQL Server keywords? What are the capacities of Access, SQL Server, and MSDE? What are the main differences between Access and SQL Server? What do I need to know about SQL Server 2000 SP4? Where else can I learn about SQL Server? Where is SP4 for SQL Server 2000? Why am I having problems with SQL Server 2000 SP3 / SP3a? Why can't I install SQL Server on Windows Server 2003? Why can't I install SQL Server on Windows XP? Why can't I use LIKE '%datepart%' queries? Why do I get "Login failed for user '\'."? Why do I get 'object could not be found' or 'invalid object name'? Why do I get errors about master..spt_values? Why do I get script errors in Enterprise Manager's 'taskpad' view? Why do I get SQLSetConnectAttr Failed errors? Why do I have problems with views after altering the base table? Why does EM crash when I get an error in a stored procedure? Why does Enterprise Manager return 'Invalid cursor state'? Why does my DELETE query not work? Why does sp_spaceused return inaccurate values? Why is Enterprise Manager slow at expanding my database list? Why is my app slow after upgrading from SQL Server 7 to 2000? Why is tempdb full, and how can I prevent this from happening? Why should I consider using an auxiliary numbers table?