Search results matching tags 'T-SQL', 'SQL', 'SQL 2008', and 'SQL Server 2008'

Calculating Holidays in SQL

Mike C — Thu, 25 Feb 2010 21:51:00 GMT

Ask about nearly any kind of SQL-based date calculation in the newsgroups, and you'll likely get responses that include use of an auxiliary calendar table. It's a really good idea, and something I highly recommend to anyone who has to do date-based calculations on the server. For those who do dimensional modeling, you'll probably notice the auxiliary calendar table closely resembles a Time dimension with one-day intervals. I won't get into too much detail on it, since ASPFAQ and the newsgroups cover it well.

What I do want to talk about is calculating holidays, which can be important when populating your auxiliary calendar table. When you create an auxiliary calendar table you may need to perform calculations based on business days, for instance; or you may need to schedule activities before, after, or even during certain holidays. There are two types of holidays that I'll address:

Fixed holidays that fall on the same day every year
Floating holidays for which the date can change from year to year

Fixed holidays include holidays that always fall on the same day each year. Some examples of fixed holidays include Christmas (always December 25 in the West), Canada Day (always July 1), U.S. Independence Day (always July 4). These are fairly easy to detect and set in your auxiliary calendar table. Just look for the static month + day combinations in your table and update it accordingly.

Floating holidays are far more interesting and difficult to calculate. Take Easter. Easter actually has two definitions. The popular definition is:

"Easter Day is the first Sunday after the full moon that occurs next after the vernal equinox"

This is not the exact ecclesiastical definition though. The full moon here is not the astronomical full moon but an "ecclesiastical moon" that's determined through precalculated lookup tables. The ecclesiastical rules are:

Easter falls on the first Sunday following the first ecclesiastical full moon that occurs on or after the day of the vernal equinox
This particular ecclesiastical full moon is the 14th day of a tabular lunation (new moon)
The vernal equinox is fixed as March 21

I'm not going to recount the long and painful history leading to the modern definition of Easter--but the details can be found on Wikipedia if you're interested. The main point is that Easter calculation is fairly complex. The udf_CalculateEaster function below accepts the year (integer) as a parameter and returns the date of Easter for that year (datetime). Notice the large number of calculations required to make this function work:

CREATE FUNCTION dbo.udf_CalculateEaster
(
@Year INT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Date DATETIME,
    @c INT,
    @n INT,
    @i INT,
    @k INT,
    @j INT,
    @l INT,
    @m INT,
    @d INT;

    SELECT @n = @Year - 19 * (@Year / 19),
    @c = @Year / 100,
    @k = (@c - 17) / 25,
    @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15,
    @i = @i - 30 * (@i / 30),
    @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)),
    @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4,
    @j = @j - 7 * (@j / 7),
    @l = @i - @j,
    @m = 3 + (@l + 40) / 44,
    @d = @l + 28 - 31 * (@m / 4),
    @Date = CAST
    (
      CAST(@Year AS CHAR(4)) +
      RIGHT
      (
        '0' + CAST
        (
          @m AS VARCHAR(2)
        ), 2
      ) +
      RIGHT
      (
        '0' +CAST
        (
          @d AS VARCHAR(2)
        ), 2
      ) AS DATETIME
    );
    RETURN @Date;
END;
GO

If you want the date for Easter 2010, just run a query like the following:

SELECT dbo.udf_CalculateEaster (2010);
GO

The result is 2010-04-04.

Another somewhat daunting type of floating holiday is the type that is based on the Nth weekday of the month. These holidays are defined using terms like "the 4th Thursday of November" (U.S. Thanksgiving), "the 3rd Monday in January" (U.S. Martin Luther King Day), or even "the *last* Monday in May" (U.S. Memorial Day). These floating holidays are not as hard to calculate as Easter, but still a little tricky (particularly Memorial Day).

To calculate these dates we need a function like udf_nthWeekDay below. This function accepts 4 parameters:

@n = integer that represents which instance of the weekday you're looking for. This would be the 4 in "4th Thursday in November".
@weekDay = 3-character day of week ("SUN" = Sunday, "MON" = Monday, etc.) I used character abbreviations instead of numbers just to make it a little easier.
@year = integer year for which to calculate
@month = integer month for which to calculate (1 = January, 2 = February, etc.)

As you can see below, the Nth weekday calculation is pretty simple.

CREATE FUNCTION dbo.udf_nthWeekDay
(
@n       INT,
@weekDay CHAR(3),
@year    INT,
@month   INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @date    DATETIME,
    @dow         INT,
    @offset      INT,
    @wd          INT;

SELECT @wd = CASE @weekDay
      WHEN 'SUN' THEN 1
      WHEN 'MON' THEN 2
      WHEN 'TUE' THEN 3
      WHEN 'WED' THEN 4
      WHEN 'THU' THEN 5
      WHEN 'FRI' THEN 6
      WHEN 'SAT' THEN 7
    END,
    @date = CAST
    (
      CAST(@year AS VARCHAR(4)) +
      RIGHT
      (
        '0' + CAST
        (
          @month AS VARCHAR(2)
        ), 2
      ) +
      '01' AS DATETIME
    ),
    @dow = DATEPART(dw, @date),
    @offset = @wd - @dow,
    @date = DATEADD(day, @offset + (@n - CASE WHEN @offset >= 0 THEN 1 ELSE 0 END) * 7, @date);
RETURN @date;
END;
GO

To get the date for Thanksgiving 2024 you could call the function like this:

SELECT dbo.udf_nthWeekDay
(
    4,
    'THU',
    2024,
    11
);

The result is 2024-11-28. Now for a holiday like U.S. Memorial Day ("the last Monday in May") the calculation is a little trickier. You have no idea up front how many Mondays there are in any given May. As an example, in 2010 and 2011 there are 5 Mondays in May; in 2012 and 2013 there are only 4. You could try to figure out the number of Mondays in May of the given year, but that's way too much work.

There's one thing we know about the last Monday in May with absolute certainty: after the last Monday in May, the next Monday we encounter will *always* be the first Monday in June. So the easier way to figure out the last Monday in May is to calculate the first Monday in June and then back into it (subtract 7 days), like this:

SELECT DATEADD
(
day,
-7,
dbo.udf_nthWeekDay
(
    1,
    'MON',
    2012,
    6
)
);

By calculating the date of the first Monday in June 2012, we can then subtract 7 days to get the date of the last Monday of May: 2012-05-28.

You can use the functions to calculate holidays for your auxiliary calendar table. I covered U.S. federal (and some other national) holidays in this one, but it can be extended to cover other national, state, and local holidays.

Parent-Child Build Scripts with SQLCMD

Mike C — Sun, 10 Jan 2010 18:33:00 GMT

On the SQL Server public programming newsgroup someone recently posted a question about an SSMS error ("Cannot parse script. 'System.OutOfMemoryException' thrown.") I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post a solution to a puzzling situation, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.

When I create database build scripts, I use the SQLCMD utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own commands, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (exception: you can run SSMS in SQLCMD mode, but that’s another story). These special SQLCMD commands all start with a ":" at the front of the line.

The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:

The \Scripts directory contains a Create.All.Sql script. This script uses the SQLCMD run command to execute the Database\Create.Database.Sql script, the Create.All.Schemas.Sql script, and so on. The Create.All.Schemas.Sql script calls the Person.Schema.Sql and Sales.Schema.Sql scripts in turn. The other Create.All.* scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:

/*
    Create All Items
*/


:r Database\Create.Database.sql
:r Schemas\Create.All.Schemas.sql
:r Types\Create.All.Types.sql
:r Tables\Create.All.Tables.sql

Each :r command kicks off the next level of child packages in turn.

SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's -v command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named environment you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.

In the example I've used a scripting variable named database. You can set the value of the database variable from the command line with the -v option. In the example below I set the database variable to the value "Test".

The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case Create.All.Sql) or from any child scripts that are run (like Create.Database.Sql, Create.All.Schemas.Sql, Person.Schema.Sql and Sales.Schema.Sql). Here’s the Create.Database.Sql script from the example:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE $(database);
GO

The scripting variable is accessed in the script with $(database). The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE Test;
GO

One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO"). If not you could end up with one script running into another and get some strange, not-very-helpful error messages.

Another thing you need to know is that scripting variables are replaced wholesale with their replacement text. This makes them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.

I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.

Mission Accomplished: NJSQL Saturday Event

Mike C — Sun, 25 Oct 2009 00:03:00 GMT

Thanks to all those who made the NJSQL Saturday event held in Iselin, NJ today a success!

Those lucky enough to attend were given a sneak preview of one of the best young presenters out there--MVP Jacob Sebastian--who will also be speaking at PASS. I have to hand it to Jacob: he gave one of the best presentations on a SQL Server topic I think I've seen yet. Between yours truly, Jacob Sebastian, and Robert Pearl, attendees learned best practices for defensive coding and error handling (XACT_ABORT anybody?), learned the concepts behind SQL Server's new spatial data types and saw how to make use them in conjunction with Web-based mapping apps (where's that A Train run again?), and learned a bit about auditing and the internals of the transaction log and CDC.

Thanks to NJSQL leader and event coordinator Melissa Demsak, attendees had plenty to eat and everyone got a chance to win several door prizes including t-shirts, books, and copies of Windows 7 Pro and Ultimate (Steve Ballmer's signature edition, no less)!

For those who asked for it, I'll be cleaning up my sample code and sample geospatial database to shrink it down and pass it along (with the other presentation materials) to Melissa for posting. Currently the sample database is about 99 MB in size, but it contains several tables that aren't really necessary for the demo code to run. Today I presented on the SQL Server geospatial "what" and "why"; at the NJSQL UG meeting in November I'll be diving into the "how". Check back at http://www.njsql.org for details.

Thanks again to all our sponsors and the NJSQL and NYC SQL Server UG for making this event happen!