Tag Archive for: database

Grouping with ROW_NUMBER

The new ranking functions in SQL Server 2005 have great impact on how problems can be solved. Here is just another example of that.

Given a table with events at different venues (all event dates are unique), find the duration intervals of start/end date of uninterrupted performances at a given venue. Solving this without the use of ROW_NUMBER would require a lot more complex SQL, not to mention that this solution has excellent performance. Running a test with 10 years of random sample data completes on average 15 milliseconds.

-- Create the sample events table

CREATE TABLE Events

(event_date DATETIME NOT NULL PRIMARY KEY,

 event_venue VARCHAR(20) NOT NULL);

 

-- Insert venue event dates

INSERT INTO Events VALUES ('20080101', 'The Palace');

INSERT INTO Events VALUES ('20080201', 'The Palace');

INSERT INTO Events VALUES ('20080301', 'The Palace');

INSERT INTO Events VALUES ('20080401', 'The Palace');

INSERT INTO Events VALUES ('20080501', 'The Palace');

INSERT INTO Events VALUES ('20080601', 'Fox Theater');

INSERT INTO Events VALUES ('20080701', 'Fox Theater');

INSERT INTO Events VALUES ('20080801', 'Grand Hall');

INSERT INTO Events VALUES ('20080901', 'Grand Hall');

INSERT INTO Events VALUES ('20081001', 'Grand Hall');

INSERT INTO Events VALUES ('20081101', 'The Palace');

INSERT INTO Events VALUES ('20071201', 'The River Place');

INSERT INTO Events VALUES ('20081202', 'The River Place');

 

-- Group the event period dates at each venue

SELECT MIN(event_venue) AS venue,

       MIN(event_date) AS venue_start_date,

       MAX(event_date) AS venue_end_date

FROM (

SELECT event_venue , event_date ,

       ROW_NUMBER() OVER (ORDER BY event_venue, event_date) -

       ROW_NUMBER() OVER (PARTITION BY event_venue

                          ORDER BY event_date),

       ROW_NUMBER() OVER (ORDER BY event_date) -

       ROW_NUMBER() OVER (PARTITION BY event_venue

                          ORDER BY event_date)

FROM Events) AS X(event_venue, event_date, grp1, grp2)

GROUP BY grp1, grp2;

 

/*

 

-- Results

 

venue                venue_start_date venue_end_date

-------------------- ----------------- --------------

The River Place     2007-12-01        2007-12-01

The Palace           2008-01-01        2008-05-01

Fox Theater         2008-06-01        2008-07-01

The Palace           2008-11-01        2008-11-01

Grand Hall           2008-08-01        2008-10-01

The River Place     2008-12-02        2008-12-02

 

*/

Updates with CTE

Performing updates on columns based on values from another table is a very common need. Using the ANSI UPDATE normally requires multiple subqueries, which can be very inefficient especially if multiple filters have to be applied. The Microsoft specific UPDATE with JOIN is one solution. However, common table expressions provide a very elegant alternative, which has the same efficient plan as UPDATE with JOIN, but is much easier to read and maintain. The sample below demonstrates how to perform update based on another table using join and CTE. Using MERGE in SQL Server 2008 will make it even better.

CREATE TABLE Products (

 sku CHAR(5) NOT NULL PRIMARY KEY,

 product_desc VARCHAR(35) NOT NULL,

 price DECIMAL(12, 2) NOT NULL DEFAULT 0);

 

CREATE TABLE ProductUpdates (

 sku CHAR(5) NOT NULL PRIMARY KEY,

 product_desc VARCHAR(35) NOT NULL,

 price DECIMAL(12, 2) NOT NULL DEFAULT 0,

 effective_date DATETIME NOT NULL);

 

INSERT INTO Products VALUES ('CHS01', 'Child seat', 25.50);

INSERT INTO Products VALUES ('CUP03', 'Water cup', 5.25);

INSERT INTO Products VALUES ('HOL01', 'Cup holder', 3.50);

 

INSERT INTO ProductUpdates VALUES ('CHS01', 'Child seat with cushion', 26.95, '20080301');

INSERT INTO ProductUpdates VALUES ('CUP03', 'Water cup with handle', 6.25, '20080405');

 

-- Update all current product descriptions and prices

-- with updates that have effective date past today (March 12, 2008)

WITH Updates

AS

(SELECT P.product_desc,

        P.price,

        U.product_desc AS new_product_desc,

        U.price AS new_price

 FROM Products AS P

 JOIN ProductUpdates AS U

   ON P.sku = U.sku

 WHERE U.effective_date < CURRENT_TIMESTAMP)

UPDATE Updates

SET product_desc = new_product_desc,

    price = new_price;

 

SELECT sku, product_desc, price

FROM Products;

 

-- Results

sku   product_desc            price

----- ----------------------- -----

CHS01 Child seat with cushion 26.95

CUP03 Water cup                5.25

HOL01 Cup holder               3.50       

Extracting List Item from Delimited String

Performing string manipulations is not the best use of T-SQL. But there are a lot of operations that can be done using pure SQL. Here is one example. Given an input string that has list elements separated by delimiter, slice the list into individual element, and provided index parameter return the matching item based on index position in the list, NULL if the index was not found, or the entire string if the input index is 0. For this method we need utility table with numbers.

-- Create sample utility table with numbers

CREATE TABLE Numbers (

 n INT PRIMARY KEY)

 

INSERT INTO Numbers

SELECT number

FROM master..spt_values

WHERE type = 'P'

  AND number BETWEEN 1 AND 100

 

GO

 

-- Extract function to perform the string split

CREATE FUNCTION dbo.Extract(

 @string VARCHAR(200),

 @delimiter CHAR(1),

 @idx INT)

RETURNS VARCHAR(200)

AS

BEGIN

 

RETURN

 CASE @idx

 WHEN 0 THEN @string

 ELSE

 (SELECT string

  FROM

      (SELECT SUBSTRING(@string, n,

             CHARINDEX( @delimiter, @string + @delimiter, n ) - n ),

             n + 1 - LEN(REPLACE(LEFT(@string, n), @delimiter, ''))

       FROM Numbers

       WHERE SUBSTRING(@delimiter + @string, n, 1) = @delimiter

         AND n < LEN(@string) + 1) AS T(string, idx)

  WHERE idx = @idx)

 END

 

END

GO

 

-- Test

DECLARE @foo VARCHAR(100)

SET @foo = 'Joe*Doe*123 Main Street'

 

SELECT dbo.Extract(@foo, '*', 2) -- returns 'Doe'

SELECT dbo.Extract(@foo, '*', 3) -- returns '123 Main Street'

SELECT dbo.Extract(@foo, '*', 0) -- returns entire string

SELECT dbo.Extract(@foo, '*', 9) -- returns null

If running SQL Server 2005 this can be accomplished much easier using CLR:

using System.Collections;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public class CLRSplit

{

    [Microsoft.SqlServer.Server.SqlFunction()]

    public static string extract(string str, char delimiter, int index)

    {

        if (index == 0)

        { return str; }

        try

        {

            string[] list = new string[100];

            list = str.Split(new char[] { delimiter });

            return list[index - 1];

        }

        catch

        {

            return null;

        }

    }

}

Shortest Path for Friend Connections

An interesting problem to solve is finding relation paths in contact management systems. This is a limited case of the Dijkstra’s algorithm for finding the shortest path in a graph. Here we always have cost of 1 for each path and it is a two-way relation between the nodes. To put this in human readable format, the problem is to find the distance between friends, based on relationships defined. If A is friend with B and then B is friend with C, the path between A and C is A->B->C with distance 2.

Here is one solution using recursive CTEs in SQL Server 2005. The method is based on building relationship paths between all connected nodes and then searching the path for friend connections. If this searching if frequent, then the path can be materialized in a column.

-- Sample table with data

CREATE TABLE Contacts (

 c_from CHAR(1),

 c_to CHAR(1),

 PRIMARY KEY (c_from, c_to));

 

INSERT    INTO Contacts

SELECT    'A', 'B' UNION ALL

SELECT    'B', 'D' UNION ALL

SELECT    'C', 'A' UNION ALL

SELECT    'C', 'E' UNION ALL

SELECT    'G', 'C' UNION ALL

SELECT    'B', 'G' UNION ALL

SELECT    'F', 'D' UNION ALL

SELECT    'E', 'F';

 

-- Table to store paths

CREATE TABLE Paths (c_path VARCHAR(200) PRIMARY KEY);

 

-- Recursive CTE to populate the paths

WITH PathCTE

AS

(SELECT c_from, c_to,

        CAST('.' + CAST(c_from AS VARCHAR(8)) + '.' +

        CAST(c_to AS VARCHAR(8)) + '.' AS VARCHAR(200)) AS c_path

 FROM Contacts AS C1

 UNION ALL

 SELECT C.c_from, C.c_to,

        CAST(P.c_path + C.c_to + '.' AS VARCHAR(200))

 FROM PathCTE AS P

 JOIN Contacts AS C

   ON P.c_to = C.c_from

 WHERE P.c_path NOT LIKE '%.' +

                    CAST(C.c_from AS VARCHAR(10)) +

                    '.' +

                    CAST(C.c_to AS VARCHAR(10)) +

                    '.%')

INSERT INTO Paths

SELECT c_path FROM PathCTE;

 

-- Show all paths between B and D

SELECT c_path

FROM Paths

WHERE c_path LIKE '.B.%'

  AND c_path LIKE '%.D.';

 

-- Shortest path distance, longest path distance, and number of paths

SELECT MIN(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2) AS shortest_distance,

       MAX(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2) AS longest_distance,

       COUNT(*) AS paths_cnt

FROM Paths

WHERE c_path LIKE '.B.%'

  AND c_path LIKE '%.D.';

 

-- Results

c_path

--------------

.B.D.

.B.G.C.A.B.D.

.B.G.C.E.F.D.

 

 

shortest_distance  longest_distance  paths_cnt

----------------- ---------------- -----------

1                  5                3

It is good to note that this method does not make effort to avoid reusing paths to reach a destination. If needed this can be handled by additional condition in the recursive CTE.

Column Alias Based on Variable

Although formatting output belongs to the reporting or front-end interface, occasionally there could be the need to change a column alias based on variable. Since T-SQL does not support proving variable as column alias in a query, here are two methods to handle that.

CREATE TABLE Foo (

 keycol INT PRIMARY KEY,

 datacol CHAR(1))

 

INSERT INTO Foo VALUES (1, 'a')

INSERT INTO Foo VALUES (2, 'b')

 

DECLARE @column_alias VARCHAR(30)

SET @column_alias = 'new_title'

 

-- 1). Using dynamic SQL

DECLARE @sql NVARCHAR(200)

 

SET @sql = 'SELECT keycol, datacol AS ' + @column_alias + ' FROM Foo'

 

EXEC sp_executesql @sql

 

-- 2). Using results table and renaming the column

CREATE TABLE Results (

 keycol INT PRIMARY KEY,

 datacol CHAR(1))

 

INSERT INTO Results

SELECT keycol, datacol

FROM Foo

 

EXEC sp_rename 'Results.datacol', @column_alias, 'COLUMN'

 

SELECT * FROM Results

Performing UPSERT in T-SQL

Very often there is the need to check if a key value exists to perform an update, and if it does not exist to insert new data. The upcoming SQL Server 2008 provides the MERGE statement (MERGE actually allows to do more: simultaneous UPDATE, INSERT and/or DELETE operations on the table), but until it is released we have to wait.

Here is just one way to implement in the current versions of T-SQL.

CREATE TABLE Foo (

 keycol INT PRIMARY KEY,

 datacol CHAR(1) NOT NULL);

 

-- Sample data

INSERT INTO Foo VALUES (1, 'a');

INSERT INTO Foo VALUES (2, 'b');

INSERT INTO Foo VALUES (4, 'd');

 

-- New values to insert/update

DECLARE @key INT;

DECLARE @data CHAR(1);

 

-- New key, will perform insert

SET @key = 3;

SET @data = 'c';

 

BEGIN TRAN

 

-- Try update

UPDATE Foo WITH (SERIALIZABLE)

SET datacol = @data

WHERE keycol = @key;

 

-- If no rows updated then must be new value, perform insert

IF @@ROWCOUNT = 0

INSERT INTO Foo VALUES (@key, @data);

 

COMMIT

 

-- Existing key, will perform update

SET @key = 4;

SET @data = 'x';

 

BEGIN TRAN

 

-- Try update

UPDATE Foo WITH (SERIALIZABLE)

SET datacol = @data

WHERE keycol = @key;

 

-- If no rows updated then must be new value, perform insert

IF @@ROWCOUNT = 0

INSERT INTO Foo VALUES (@key, @data);

 

COMMIT

 

SELECT keycol, datacol

FROM Foo;

The SERIALIZABLE hint here is very important to avoid deadlocks.

Table Value Constructors in SQL Server 2008

One of the new features of SQL Server 2008 is the support for table value constructors (part of ANSI SQL). Here are a couple quick examples of using them.

-- Populate sample table

 

CREATE TABLE Foo (

  keycol INT PRIMARY KEY,

  datacol VARCHAR(30));

In the past, populating table rows was done like this:

INSERT INTO Foo VALUES (1, 'Books');

INSERT INTO Foo VALUES (2, 'CDs');

INSERT INTO Foo VALUES (3, 'DVDs');

 

-- or

 

INSERT INTO Foo (keycol, datacol)

SELECT 1, 'Books'

UNION ALL

SELECT 2, 'CDs'

UNION ALL

SELECT 3, 'DVDs';

 

-- or using on the fly

 

SELECT keycol, datacol

FROM ( SELECT 1, 'Books'

       UNION ALL

       SELECT 2, 'CDs'

       UNION ALL

       SELECT 3, 'DVDs') AS Foo (keycol, datacol);

Here is how the same can be done with SQL Server 2008 table value constructors:

INSERT INTO Foo (keycol, datacol)

VALUES (1, 'Books'), (2, 'CDs'), (3, 'DVDs');

 

-- or using on the fly

 

SELECT keycol, datacol

FROM ( VALUES (1, 'Books'),

              (2, 'CDs'),

              (3, 'DVDs') ) AS Foo (keycol, datacol);

 

-- and CTE version

 

WITH Foo (keycol, datacol)

AS( SELECT *

    FROM VALUES (1, 'Books'),

                    (2, 'CDs'),

                    (3, 'DVDs') ) AS F (keycol, datacol))

SELECT keycol, datacol

FROM Foo;

Another interesting option is to derive a row value from a subquery, like this:

INSERT INTO Foo (keycol, datacol)

VALUES ((SELECT MAX(keycol) + 1 FROM Foo), 'Tapes');

Still not possible, but maybe in the next version (or Service Pack) we can see vector expressions in predicates and UPDATE, like:

SELECT keycol, datacol

FROM Foo

WHERE (keycol, datacol) IN (SELECT keycol, datacol FROM Foo2);

 

-- or

 

UPDATE Foo

SET (keycol, datacol) = (SELECT keycol, datacol FROM Foo2);

Calculating Work Hours

Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. Normally this is best done using an auxiliary Calendar table which provides easy way for calculation and flexibility to account for holidays and other events. However, sometimes the requirements are simplified and the point of interest is only the work hours between two dates, accounting only for weekends, without holidays. Here is one way to perform this calculation using the date and time functions in SQL Server. Note that for this example the week start day is logically set to Monday by adding (@@DATEFIRST – 1) to the start date.

CREATE FUNCTION dbo.CalcWorkHours (

 @start_date DATETIME,

 @end_date DATETIME)

RETURNS INT

AS

BEGIN

  RETURN

  (SELECT ((total_days / 7) * 5 + total_days % 7 -

           CASE WHEN 6 BETWEEN start_weekday AND end_weekday

                THEN 1 ELSE 0 END -

           CASE WHEN 7 BETWEEN start_weekday AND end_weekday

                THEN 1 ELSE 0 END) * 8

    FROM (SELECT total_days, start_weekday,

                start_weekday + total_days % 7 - 1

          FROM (SELECT DATEDIFF(day, @start_date, @end_date) + 1,

                       DATEPART(WEEKDAY, @start_date + @@DATEFIRST - 1)

               ) AS T(total_days, start_weekday)

        ) AS D(total_days, start_weekday, end_weekday)

  );

END

GO

 

SELECT dbo.CalcWorkHours('20080401', '20080421')

 

/*

 

-- Result

120

 

*/

Unique Column with Multiple NULLs

A very frequent need is to define unique column that allows multiple NULL values. Since the UNIQUE constraint considers two NULL values the same, it allows only a single NULL value.

Here is one solution for this problem using a view filtering on non NULL values with UNIQUE index on the view.

CREATE TABLE dbo.Foo (

 keycol INT NULL,

 datacol CHAR(1));

 

GO

 

CREATE VIEW UniqueFoo WITH SCHEMABINDING

AS

SELECT keycol, datacol

FROM dbo.Foo

WHERE keycol IS NOT NULL;

 

GO

 

CREATE UNIQUE CLUSTERED INDEX ix_UniqueFoo

ON UniqueFoo(keycol);

GO

 

-- OK, multiple NULL values allowed

INSERT INTO dbo.Foo VALUES(1, 'a');

INSERT INTO dbo.Foo VALUES(NULL, 'b');

INSERT INTO dbo.Foo VALUES(NULL, 'c');

 

GO

 

-- Error, attempt to insert duplicate keycol value

INSERT INTO dbo.Foo VALUES(1, 'd');

 

/*

 

Msg 2601, Level 14, State 1, Line 2

Cannot insert duplicate key row in object 'dbo.UniqueFoo' with unique index 'ix_UniqueFoo'.

The statement has been terminated.

 

*/

In SQL Server 2008 this can be accomplished much easier using UNIQUE filtered index.

CREATE UNIQUE NONCLUSTERED INDEX ix_Foo

  ON Foo (keycol)

  WHERE keycol IS NOT NULL;

Auxiliary Tables

Auxiliary tables (also referred as utility or helper tables) are a great tool for many needs in SQL. The good part is they are portable between systems, easy to create and normally do not take much storage. There are many benefits: faster SQL, simplified problem solutions, provide flexibility and options that are not easy to replicate with pure SQL code, and in many cases will help to avoid procedural coding and offer a set based way.

Here are samples for creating the two most common auxiliary tables: table with numbers and calendar table, as well as a couple brief examples of utilizing them.

-- Create table structure

CREATE TABLE Numbers (

 nbr INT NOT NULL

   CONSTRAINT df_numbers_nbr

   DEFAULT 1

   CONSTRAINT ck_numbers_nbr

   CHECK (nbr > 0)

   CONSTRAINT pk_numbers

   PRIMARY KEY);

 

-- Populate via cross joining CTEs

-- Very fast, can be implemented as UDF

WITH

Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),

Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),

Nums (n) AS

(SELECT ROW_NUMBER() OVER(ORDER BY n)

 FROM Num6)

INSERT INTO Numbers (nbr)

SELECT n FROM Nums

WHERE n <= 1000000;

 

-- Populate via generating digits and multiplication

-- for tens, hundreds, thousands, etc.

WITH Digits (n) AS

( SELECT 0 UNION SELECT 1 UNION SELECT 2

  UNION SELECT 3 UNION SELECT 4 UNION SELECT 5

  UNION SELECT 6 UNION SELECT 7 UNION SELECT 8

  UNION SELECT 9),

Nums (n) AS

( SELECT VII.n * 1000000 +

        VI.n * 100000 +

        V.n * 10000 +

        IV.n * 1000 +

        III.n * 100 +

        II.n * 10 +

        I.n

  FROM Digits AS I

  CROSS JOIN Digits AS II

  CROSS JOIN Digits AS III

  CROSS JOIN Digits AS IV

  CROSS JOIN Digits AS V

  CROSS JOIN Digits AS VI

  CROSS JOIN Digits AS VII

)

INSERT INTO Numbers (nbr)

SELECT n FROM Nums

WHERE n BETWEEN 1 AND 1000000;

 

-- One example of using table with numbers

-- to parse a list string to table format

-- with index for each element

DECLARE @list VARCHAR(100);

DECLARE @delimiter CHAR(1);

 

SET @list = 'Mon,Tue,Wed,Thu,Fri,Sat,Sun';

SET @delimiter = ',';

 

-- Split the list based on delimiter

SELECT SUBSTRING(@list, nbr,

                CHARINDEX(@delimiter,

                           @list + @delimiter, nbr) - nbr) AS list_value,

       nbr + 1 - LEN(REPLACE(LEFT(@list, nbr), @delimiter, '')) AS list_index

FROM Numbers

WHERE SUBSTRING(@delimiter + @list, nbr, 1) = @delimiter

  AND nbr < LEN(@list) + 1;

 

/*

 

-- Results

list_value list_index

---------- -----------

Mon        1

Tue        2

Wed        3

Thu        4

Fri        5

Sat        6

Sun        7

 

 

*/

 

-- Another example to clean non-numeric characters from string

-- The table with numbers is used to slice the string to characters

-- then non-numeric characters are eliminated and the string

-- is concatenated back using FOR XML PATH with blank element name

DECLARE @string VARCHAR(30);

 

SET @string = '01234ab23-97z@';

 

SELECT SUBSTRING(@string, nbr, 1)

FROM Numbers

WHERE nbr <= LEN(@string)

  AND SUBSTRING(@string, nbr, 1) LIKE '[0-9]'

FOR XML PATH('');

 

/*

 

-- Result

012342397

 

*/

 

-- Calendar table structure

CREATE TABLE Calendar (

 cdate DATETIME NOT NULL

   CONSTRAINT df_calendar_cdate

   DEFAULT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)

   CONSTRAINT ck_calendar_cdate

   CHECK (cdate = DATEADD(day, DATEDIFF(day, 0, cdate), 0))

   CONSTRAINT pk_calendar

   PRIMARY KEY,

 cweek INT NOT NULL

   CONSTRAINT df_calendar_cweek

   DEFAULT 1

   CONSTRAINT ck_calendar_cweek

   CHECK (cweek BETWEEN 1 AND 53),

 cweekday INT NOT NULL

   CONSTRAINT df_calendar_cweekday

   DEFAULT 1

   CONSTRAINT ck_calendar_cweekday

   CHECK (cweekday BETWEEN 1 AND 7),

 cyear INT NOT NULL

   CONSTRAINT df_calendar_cyear

   DEFAULT 1900

   CONSTRAINT ck_calendar_cyear

   CHECK (cyear >= 1900),

 cquarter INT NOT NULL

   CONSTRAINT df_calendar_cquarter

   DEFAULT 1

   CONSTRAINT ck_calendar_cquarter

   CHECK (cquarter BETWEEN 1 AND 4),

 cmonth INT NOT NULL

   CONSTRAINT df_calendar_cmonth

   DEFAULT 1

   CONSTRAINT ck_calendar_cmonth

   CHECK (cmonth BETWEEN 1 AND 12),

 cday INT NOT NULL

   CONSTRAINT df_calendar_cday

   DEFAULT 1

   CONSTRAINT ck_calendar_cday

   CHECK (cday BETWEEN 1 AND 31),

 cday_of_year INT NOT NULL

   CONSTRAINT df_calendar_cday_of_year

   DEFAULT 1

   CONSTRAINT ck_calendar_cday_of_year

   CHECK (cday_of_year BETWEEN 1 AND 366),

 cmonth_name VARCHAR(9) NOT NULL

   CONSTRAINT df_calendar_cmonth_name

   DEFAULT 'N/A',

 cday_name VARCHAR(9) NOT NULL

   CONSTRAINT df_calendar_cday_name

   DEFAULT 'N/A',

 holiday VARCHAR(35) NOT NULL

   CONSTRAINT df_calendar_holiday

   DEFAULT 'N/A');

 

 

-- Calculate number of days for 10 years from '20080101'

SELECT DATEDIFF(day, '20080101', '20180101') AS days;

 

/*

 

-- Result

3653

 

*/

 

-- Insert 3653 days, which is 10 years

-- Another use for table with numbers

INSERT INTO Calendar (cdate)

SELECT DATEADD(dd, N.nbr - 1, '20080101')

FROM Numbers AS N

WHERE N.nbr <= 3653;

 

-- Populate calendar columns

-- First day of the week is logically set to Monday

-- Can be enhanced to have column for

-- ISO week as it differs from internal SQL Server

-- week date part

UPDATE Calendar

SET cweek = DATEPART(wk, cdate - DATEPART(dw, cdate + @@DATEFIRST - 1) + 1),

    cweekday = DATEPART(dw, cdate + @@DATEFIRST - 1),

    cyear = DATEPART(yyyy, cdate),

    cquarter = DATEPART(qq, cdate),

    cmonth = DATEPART(mm, cdate),

    cday = DATEPART(day, cdate),

    cday_of_year = DATEPART(dy, cdate),

    cmonth_name = DATENAME(mm, cdate),

    cday_name = DATENAME(dw, cdate);

 

-- Add some fixed date official holidays

-- See http://en.wikipedia.org/wiki/List_of_holidays_by_country

-- for full list by country

 

-- New Year's Day

UPDATE Calendar

SET holiday = 'New Year''s Day'

WHERE cday = 1

  AND cmonth = 1;

 

-- Christmas Eve

UPDATE Calendar

SET holiday = 'Christmas Eve'

WHERE cday = 24

  AND cmonth = 12;

 

-- Christmas Day

UPDATE Calendar

SET holiday = 'Christmas Day'

WHERE cday = 25

  AND cmonth = 12;

 

-- New Year's Eve

UPDATE Calendar

SET holiday = 'New Year''s Eve'

WHERE cday = 31

  AND cmonth = 12;

 

-- Get work days for year 2008

-- Note that not all official holidays are defined

-- and this is only counting non-weekend days

-- and existing holidays

SELECT COUNT(*) AS work_days

FROM Calendar

WHERE cyear = 2008

  AND cweekday NOT IN (6, 7) -- 6 = Saturday; 7 = Sunday

  AND holiday = 'N/A';

 

/*

 

-- Result

work_days

-----------

258

 

*/

To get ISO week calculation use the function example IsoWeek in SQL Server Books Online under CREATE FUNCTION:
http://msdn2.microsoft.com/en-us/library/ms186755.aspx

In SQL Server 2008 the DATEPART function has been enhanced and has date part parameter ISO_WEEK, which supports ISO 8601 week numbering:
http://msdn2.microsoft.com/en-us/library/ms174420(SQL.100).aspx

On SQL Server 2008 the calendar table can be modeled using the new DATE data type which will eliminate the need of constraints to handle the time portion:
http://technet.microsoft.com/en-us/library/bb630352(SQL.100).aspx