SQL SERVER – Working with Business Days in SQL Server – A Different Approach

Note: I recommend that you download XLeratorDB and try this problem along with me, this is going to be fun exercise and something new to learn.

Working with dates can be one of the more challenging aspects of IT. First, as developers we are stick with the calendar that has a non-uniform implementation of a year (approximately 365 97/400 days) causing the number of days in the year to fluctuate from year-to-year; an imprecise definition of a month (anywhere from 28 to 31 days); and requirements that our systems understand what the rest of the world means when the terms month and years are being bandied about. For example, if something is due in one month’s time does that mean in 30 days, in 4 weeks, on the same day of the month as today unless it’s not a business day, or something else?

Further, most of the time when our user are talking about days, they are not really so much concerned with the mathematical difference between two dates so much as they are concerned with the number of business days between 2 dates.  For example, when we promise overnight delivery, that promise is usually means delivery on the next business day.

SQL Server doesn’t really provide many tools that help with these type of calculations. True, SQL Server 2012 contains some interesting new date function like EOMONTH and DATEFROMPARTS, but any coding for business date logic is left to the developers

Fortunately, the team at Westclintech has a robust library of SQL Server CLR functions for handling business date logic. They have functions to calculate the:

  • next business day;
  • next n business days;
  • previous business day;
  • previous n business days;
  • first occurrence of a weekday in a month;
  • last occurrence of a weekday in a month;
  • the number of business days between 2 dates (inclusive); and
  • the number of business days between 2 dates (exclusive)

They also have a number of functions that do the following:

  • convert elapsed time to a fraction of a year;
  • count the number of days between two dates assuming 360-day year;
  • calculate Western Easter;
  • Calculate the end-of-month n months forward or backward;
  • Calculate and exact date n months forward or backward;

and many more.

I have been trying their XLeratorDB library out and it allows me to do almost any kind of date calculation that I can think use very simple TSQL statements.

Here’s a typical example of the type of information that business users take for granted but that can quite difficult to calculate in SQL Server. Let’s say you needed to produce a report that showed average daily trading volumes the total number of trading days in a year as well as the total number of trading days by month for a year. With a quick internet search you discover that the stock exchange is closed on Saturdays and Sundays and on the following holidays:

  • New Year’s Day (January 1st)
  • Martin Luther King, Jr. Day (third Monday in January)
  • Washington’s Birthday (third Monday in February)
  • Good Friday (the Friday before Western Easter)
  • Memorial Day (the last Monday in May)
  • Independence Day (July 4th)
  • Labor Day (the first Monday in September)
  • Thanksgiving Day (the fourth Thursday in November)
  • Christmas Day (December 25th)

Additionally, if a holiday falls on a Saturday it is observed on the previous Friday. If a holiday falls on a Sunday, it is observed on the subsequent Monday.

XLeratorDB has several functions that help to capture the holiday information above. In this example, I use the BUSINESSDATE, FIRSTWEEKDAY, LASTWEEKDAY, EASTER, and CALCDATE functions to calculate the holidays for the stock exchange.

SELECT x.holiday
,CAST(x.hdate AS date) AS hdate
FROM (SELECT
wct.BUSINESSDATE(wct.CALCDATE(n.y,1,1),'M',0,'G','') AS [New Year's Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,1,1),'Mon')+14 AS [MLK Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,2,1),'Mon')+14 AS [Washington's Birthday]
,wct.BUSINESSDATE(wct.EASTER(n.y),'M',0,'P','') AS [Good Friday]
,wct.LASTWEEKDAY(wct.CALCDATE(n.y,5,1),'Mon') AS [Memorial Day]
,wct.BUSINESSDATE(wct.CALCDATE(n.y,7,4),'M',0,'G','') AS [Independence Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,9,1),'Mon') AS [Labor Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,11,1),'Thu')+21 AS [Thanksgiving Day]
,wct.BUSINESSDATE(wct.CALCDATE(n.y,12,25),'M',0,'G','') AS Christmas
FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020))n(y)
)
p
CROSS APPLY (VALUES
('New Year''s Day',[New Year's Day])
,(
'MLK Day',[MLK Day])
,(
'Washington''s Birthday',[Washington's Birthday])
,(
'Good Friday',[Good Friday])
,(
'Memorial Day',[Memorial Day])
,(
'Independence Day',[Independence Day])
,(
'Labor Day', [Labor Day])
,(
'Thanksgiving Day',[Thanksgiving Day])
,(
'Christmas',Christmas)
)
x(holiday, hdate)

Here are the first few rows of the resultant table.

holiday               hdate
--------------------- ----------
New Year's Day        2013-01-01
MLK Day               2013-01-21
Washington's Birthday 2013-02-18
Good Friday           2013-03-29
Memorial Day          2013-05-27
Independence Day      2013-07-04
Labor Day             2013-09-02
Thanksgiving Day      2013-11-28
Christmas             2013-12-25
New Year's Day        2014-01-01
MLK Day               2014-01-20
Washington's Birthday 2014-02-17
Good Friday           2014-04-18
Memorial Day          2014-05-26
Independence Day      2014-07-04
Labor Day             2014-09-01
Thanksgiving Day      2014-11-27
Christmas             2014-12-25. . .

SQL Server 2012 users will notice that the XLeratorDB CALCDATE function is very much like the DATEFROMPARTS function. In many cases, I find it easier to use the CALCDATE function, as it works just like the DATE function in EXCEL, whereas the DATEFROMPARTS function is much less flexible in terms of input. When I enter DATE(2013,3,0) in EXCEL or wct.CALCDATE(2013,3,0) they both understand that I mean 2013-02-28. However, when I try this statement in SQL Server 2012

SELECT DATEFROMPARTS (2013,3,0)

I get the following result.

Msg 289, Level 16, State 1, Line 9
Cannot construct data type date, some of the arguments have values which are not valid.

Sure, I can probably code around that, but why would I want to?

XLeratorDB provides a function that store all the holidays in a scalar variable to be used in the calculation of business days. We can use this information to calculate the number of business days in a year.

DECLARE @hol AS VARCHAR(MAX) = (
SELECT wct.NBD(CAST(x.hdate AS date))
FROM (SELECT
wct.BUSINESSDATE(wct.CALCDATE(n.y,1,1),'M',0,'G','') AS [New Year's Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,1,1),'Mon')+14 AS [MLK Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,2,1),'Mon')+14 AS [Washington's Birthday]
,wct.BUSINESSDATE(wct.EASTER(n.y),'M',0,'P','') AS [Good Friday]
,wct.LASTWEEKDAY(wct.CALCDATE(n.y,5,1),'Mon') AS [Memorial Day]
,wct.BUSINESSDATE(wct.CALCDATE(n.y,7,4),'M',0,'G','') AS [Independence Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,9,1),'Mon') AS [Labor Day]
,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,11,1),'Thu')+21 AS [Thanksgiving Day]
,wct.BUSINESSDATE(wct.CALCDATE(n.y,12,25),'M',0,'G','') AS Christmas
FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020))n(y)
)
p
CROSS APPLY (VALUES
('New Year''s Day',[New Year's Day])
,(
'MLK Day',[MLK Day])
,(
'Washington''s Birthday',[Washington's Birthday])
,(
'Good Friday',[Good Friday])
,(
'Memorial Day',[Memorial Day])
,(
'Independence Day',[Independence Day])
,(
'Labor Day', [Labor Day])
,(
'Thanksgiving Day',[Thanksgiving Day])
,(
'Christmas',Christmas)
)
x(holiday, hdate)
)

Here’s a very simple example to get stated. We will calculate the number of business days in 2013 using the holiday variable that we just populated.

SELECT wct.BUSDAYS('2013-01-01','2014-01-01',@hol) AS [Business Days]

This returns the following result.

Business Days
-------------
252

If we wanted to know the number of business days for each year from 2013 to 2020, we could have used the following SQL.

SELECT n.y AS [Year]
,wct.BUSDAYS(wct.CALCDATE(n.y,01,01),wct.CALCDATE(n.y+1,01,01),@hol) AS [Business Days]
FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020))n(y)

This produces the following result.

Year Business Days
----------- -------------
2013           252
2014           252
2015           252
2016           252
2017           251
2018           252
2019           252
2020           253

Here we calculate the number of business days in each month in 2013.

SELECT DATENAME(m,wct.CALCDATE(2013,n.m,1)) AS [Month]
,wct.BUSDAYS(wct.CALCDATE(2013,n.m,1),wct.CALCDATE(2013,n.m+1,1),@hol) AS [Business Days]
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))n(m)

This produces the following result.

Month                          Business Days
------------------------------ -------------
January                                   21
February                                  19
March                                     20
April                                     22
May                                       22
June                                      20
July                                      22
August                                    22
September                                 20
October                                   23
November                                  20
December                                  21

And in this example, we explicitly calculate each business date in 2013. I am using a number-generating table-valued function from the XLeratorDB/math library to generate integer values from 1 to 252 and then passing the integer values into the BUSINESSDATE function to calculate each business date in 2013 starting from 2013-01-01.

SELECT CAST(wct.BUSINESSDATE(wct.CALCDATE(2013,1,1),'D',k.seriesvalue,'F',@hol) AS date) AS [Work day]
FROM wct.SeriesInt(1,252,NULL,NULL,NULL)k

Here are some of the rows in the resultant table.

Work day
----------
2013-01-02
2013-01-03
2013-01-04
2013-01-07
2013-01-08
2013-01-09
2013-01-10
2013-01-11
2013-01-14
2013-01-15
2013-01-16
2013-01-17
2013-01-18
2013-01-22
2013-01-23
2013-01-24
2013-01-25
2013-01-28
2013-01-29
2013-01-30
2013-01-31...
2013-12-19
2013-12-20
2013-12-23
2013-12-24
2013-12-26
2013-12-27
2013-12-30
2013-12-31

XLeratorDB also provides a function that allows me to specify what the weekend days are when I want to do the business date calculation.

In this example, I want to calculate delivery dates for packages using the holidays that I have already calculated about, but I don’t want Saturday’s to be a weekend day, as deliveries can be made on a Saturday.

SELECT n.shiptype
,CAST(wct.BUSINESSDATEWE(wct.CALCDATE(2013,11,25),'D',n.d,'F',@hol,0,NULL) AS date) AS [Expected Delivery]
FROM (VALUES (1,'Overnight'),(2,'Two-day'),(3,'Regular Ground'),(4,'Regular Gound'),(5,'Bulk'))n(d,shiptype)

This produces the following result.

shiptype       Expected Delivery
-------------- -----------------
Overnight      2013-11-26
Two-day        2013-11-27
Regular Ground 2013-11-29
Regular Gound  2013-11-30
Bulk           2013-12-02

As you can see, no delivery was predicted for Thanksgiving (2013-11-28), but Saturday (2013-11-30) was included.

The XLeratorDB business date functions are included in the XLeratorDB / financial libraries which run in SQL Server 2005, 2008, and 2012. I have explored just a small part of the functionality that is available for date calculation in SQL using these libraries and you can find out more about them by visiting www.westclintech.com where you can download a free 15-day trial. I think that these function make date calculations in SQL Server much easier. Let me know what you think.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – Working with Business Days in SQL Server – A Different Approach

  1. Hi Pinal,

    Thanks for your solutions and support. We are learning lot of things from your Blog.
    I got a very difficul problem, in SSIS i need to develop a package which migrate the data sql server to sql server, but the problem is

    i have a column like

    column1
    ————
    2
    8
    4
    16
    512
    1024
    2048

    i need to derive this column to log2(column1) means
    2^1, 2^3, 2^2, 2^4, 2^9,..
    after deriving column values should appear like

    1
    3
    2
    4
    9
    10
    11

    that means the colum values are 2power(x) i ned to derive this x , please suggest me.
    even i dint get the vb code to derive power value.
    Please please please please save me.

    • Hi Raj,

      Try this:
      SELECT LOG() / LOG(2)
      FROM

      Setting the ‘”/LOG(2)” portion simulates a different default base. Figured it was about time I gave something back to this site!
      Todd

  2. hi – i am trying to exec a report daily – all through the year.
    for the next year(first working day of the year) – i want the previous data to be deleted and exec the same proc as always it did for prev year.
    i am not able to find the first working day for every year. can you help me here with a simpler liner solution?

  3. Hi Srividya,

    I haven’t provided any SQL because there are too many unknowns with what you are trying to do, so I am only suggesting possible design approaches.
    You have a challenge here in so much that the first working day of the year is not the same in every region, in every country. In the UK, it is different between England and Scotland. It may very well be different from state to state in the US. If you want to make this portable for globalisation at all, the best way would be to have a lookup table to provide you with the values, using country codes to provide the date value you need. There will only be a handful of exceptions, and everything else can be default.
    Outside of that, if you assume that 02/01XXXX is the first possible working day everywhere,then you could simply interrogate that day of week to determine whether it is a work day or not. If not, use that date to find the next Monday and use a standard DATEADD() to advance the date value.
    Regards
    Todd

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s