//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   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)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2000.databases.aspfaq.com

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:
  • 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?
As you can imagine, all of these answers require complex iterative code that steps through and applies logic to each of the dates in a range. Or do they? 
 
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).
Now, we need to populate the values for the other columns. Initially, this article used computed columns, but Chris Hohmann pointed out that for an extra hit up front, we could improve long-term efficiency by storing actual values. So, given that you may not have included all of the columns in the above table definition, you may choose to remove some of the following lines from the UPDATE statement: 
 
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 NameNotes
dtThis 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.
isWeekdayThis 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).
isHolidayThis 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
YThis 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.
FYThis 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).
QThis 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.
MThis 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.
DThis 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.
DWThis 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.
monthnameThis 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.
daynameThis 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.
WThis 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.
UTCOffsetThis 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?

 

 


Created: 4/12/2004 | Last Updated: 8/5/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (221)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...