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

SQL SERVER – Number-Crunching with SQL Server – Exceed the Functionality of Excel

Imagine this. Your users have developed an Excel spreadsheet that extracts data from your SQL Server database, manipulates that data through the use of Excel formulas and, possibly, some VBA code which is then used to calculate P&L, hedging requirements or even risk numbers. Management comes to you and tells you that they need to get rid of the spreadsheet and that the results of the spreadsheet calculations need to be persisted on the database.

SQL Server has a very small set of functions for analyzing data. Excel has hundreds of functions for analyzing data, with many of them focused on specific financial and statistical calculations. Is it even remotely possible that you can use SQL Server to replace the complex calculations being done in a spreadsheet?

Westclintech has developed a library of functions that match or exceed the functionality of Excel’s functions and contains many functions that are not available in EXCEL. Their XLeratorDB library of functions contains over 700 functions that can be incorporated into T-SQL statements.

XLeratorDB takes advantage of the SQL CLR architecture introduced in SQL Server 2005. SQL CLR permits managed code to be compiled into the database and run alongside built-in SQL Server functions like COUNT or SUM. The Westclintech developers have taken advantage of this architecture to bring robust analytical functions to the database.

In our hypothetical spreadsheet, let’s assume that our users are using the YIELD function and that the data are extracted from a table in our database called BONDS. Here’s what the spreadsheet might look like.

We go to column G and see that it contains the following formula.

Obviously, SQL Server does not offer a native YIELD function. However, with XLeratorDB we can replicate this calculation in SQL Server with the following statement:

SELECT *,
wct.YIELD(CAST(GETDATE() AS date),Maturity,Rate,Price,100,Frequency,Basis) AS YIELD
FROM BONDS

This produces the following result.

This illustrates one of the best features about XLeratorDB; it is so easy to use. Since I knew that the spreadsheet was using the YIELD function I could use the same function with the same calling structure to do the calculation in SQL Server. I didn’t need to know anything at all about the mechanics of calculating the yield on a bond. It was pretty close to cut and paste. In fact, that’s one way to construct the SQL. Just copy the function call from the cell in the spreadsheet and paste it into SMS and change the cell references to column names. I built the SQL for this query by starting with this.

SELECT *
,
YIELD(TODAY(),B2,C2,D2,100,E2,F2)
FROM BONDS

I then changed the cell references to column names.

SELECT *
--,YIELD(TODAY(),B2,C2,D2,100,E2,F2)
,YIELD(TODAY(),Maturity,Rate,Price,100,Frequency,Basis)
FROM BONDS

Finally, I replicated the TODAY() function using GETDATE() and added the schema name to the function name.

SELECT *
--,YIELD(TODAY(),B2,C2,D2,100,E2,F2)
--,YIELD(TODAY(),Maturity,Rate,Price,100,Frequency,Basis)
,wct.YIELD(GETDATE(),Maturity,Rate,Price,100,Frequency,Basis)
FROM BONDS

Then I am able to execute the statement returning the results seen above.

The XLeratorDB libraries are heavy on financial, statistical, and mathematical functions. Where there is an analog to an Excel function, the XLeratorDB function uses the same naming conventions and calling structure as the Excel function, but there are also hundreds of additional functions for SQL Server that are not found in Excel.

You can find the functions by opening Object Explorer in SQL Server Management Studio (SSMS) and expanding the Programmability folder under the database where the functions have been installed. The  Functions folder expands to show 3 sub-folders: Table-valued Functions; Scalar-valued functions, Aggregate Functions, and System Functions. You can expand any of the first three folders to see the XLeratorDB functions.

Since the wct.YIELD function is a scalar function, we will open the Scalar-valued Functions folder, scroll down to the wct.YIELD function and and click the plus sign (+) to display the input parameters.

The functions are also Intellisense-enabled, with the input parameters displayed directly in the query tab.

The Westclintech website contains documentation for all the functions including examples that can be copied directly into a query window and executed. There are also more one hundred articles on the site which go into more detail about how some of the functions work and demonstrate some of the extensive business processes that can be done in SQL Server using XLeratorDB functions and some T-SQL.

XLeratorDB is organized into libraries: finance, statistics; math; strings; engineering; and financial options. There is also a windowing library for SQL Server 2005, 2008, and 2012 which provides functions for calculating things like running and moving averages (which were introduced in SQL Server 2012), FIFO inventory calculations, financial ratios and more, without having to use triangular joins.

To get started you can download the XLeratorDB 15-day free trial from the Westclintech web site. It is a fully-functioning, unrestricted version of the software. If you need more than 15 days to evaluate the software, you can simply download another 15-day free trial.

XLeratorDB is an easy and cost-effective way to start adding sophisticated data analysis to your SQL Server database without having to know anything more than T-SQL.

Get XLeratorDB Today and Now!

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

Personal Technology – Excel Tip: Comparing Excel Files

This guest post is by Vinod Kumar. Vinod Kumar has worked with SQL Server extensively since joining the industry over a decade ago. Working on various versionsfrom SQL Server 7.0, Oracle 7.3 and other database technologies – he now works with the Microsoft Technology Center (MTC) as a Technology Architect.

Let us read the blog post in Vinod’s own voice.


I have been writing about Excel Tips over my blog and thought it would be great to share one interesting tips here as a guest blog here. Assume a situation where you want to compare multiple excel files. Here is a typical scenario I have encountered as a common activity. Assume you are sending an Excel file with tons of data, formulae and multiple sheets. Now you are requesting your colleague to validate the file and if required change content for correctness. After receiving the file from your colleague, now you want to know what changes were made by this person to your document. Now here is a cool new addition to Excel 2013 that can help you achieve this task.

To get to this option, click the INQUIRE Tab. Incase you don’t have the INQUIRE Tab, check Options using INQUIRE blog. In that post, we discuss all the other options of INQUIRE tab.

Once you are on the INQUIRE Tab, select “Compare Files” button as shown in the figure above. This brings a dialog as below.

If you are on Windows 8 or Windows 7 OS, search for an application called “Spreadsheet Compare 2013”. Ultimately both the options lead us to the same application. If you are using the stand alone app, once the App initializes, click the “Compare files” options from the toolbar. Make sure to give two different Excel files as shown in the figure above.

After selecting the Excel Sheets, you can see the Compare tool has a number of other options to play from. We will talk about some of them later in this post.

Just below our toolbar is a colorful side-by-side comparison of both our excel sheets. We can also see the various Tab’s from each file. There is a meaning for each of our color coding which will be discussed next.

As you saw above, the color coding has a meaning. For example the bottom pane lists each of the color coding and most importantly each of the changes as compared side-by-side. The detailed information shown below can be exported using the “Export Results” options from the toolbar as a separate Excel Workbook or can be copied to clipboard to be used later.

The final piece of the puzzle is to show a graphical view of these differences results based on each category. We cannot drill down per se, but this is a great way to know that the maximum changes seem to be based on “Cell Formats” and then few “Calculated Values” have changed.

The INQUIRE option and Spreadsheet Compare 2013 tool is part of Excel 2013. So as you explore using the new version of Excel, there are many such hidden features that are worth exploring. Do let us know if you enjoyed learning a new feature today and I hope you will play around with this feature in your day-today challenges when working with Excel files.

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

SQL SERVER – Preserve Leading Zero While Coping to Excel from SSMS

Earlier I wrote two articles about how to efficiently copy data from SSMS to Excel. Since I wrote that post there are plenty of interest generated on this subject. There are a few questions I keep on getting over this subject. One of the question is how to get the leading zero preserved while copying the data from SSMS to Excel. Well it is almost the same way as my earlier post SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet. The key here is in EXCEL and not in SQL Server.

The step here is to change the format of Excel Cell to Text from Numbers and that will preserve the value of the with leading or trailing Zeros in Excel. However, I assume this is done for display purpose only because once you convert column to Text you may find it difficult to do numeric operations over the column for example Aggregation, Average etc. If you need to do the same you should either convert the columns back to Numeric in Excel or do the process in Database and export the same value as along with it as well.

However, I have seen in requirement in the real world where the user has to have a numeric value with leading Zero values in it for display purpose. Here is my suggestion, instead of manipulating numeric value in the database and converting it to character value the ideal thing to do is to store it as a numeric value only in the database. Whatever changes you want to do for display purpose should be handled at the time of the display using the format function of SQL or Application Language. Honestly, database is data layer and presentation is presentation layer – they are two different things and if possible they should not be mixed.

If due to any reason you cannot follow above advise and you need is to have append leading zeros in the database only here are two of my previous articles I suggest you to refer them. I am open to learn new tricks as these articles are almost three years old. Please share your opinion and suggestions in the comments area.

SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display

SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display

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

SQL SERVER – Copy Column Headers from Resultset – SQL in Sixty Seconds #027 – Video

SQL Server Management Studio returns results in Grid View, Text View and to the file. When we copy results from Grid View to Excel there is a common complaint that the column  header displayed in resultset is not copied to the Excel. I often spend time in performance tuning databases and I run many DMV’s in SSMS to get a quick view of the server. In my case it is almost certain that I need all the time column headers when I copy my data to excel or any other place.

SQL Server Management Studio have two different ways to do this.

Method 1: Ad-hoc

When result is rendered you can right click on the resultset and click on Copy Header. This will copy the headers along with the resultset. Additionally, you can use the shortcut key CTRL+SHIFT+C for coping column headers along with the resultset.

Method 2: Option Setting at SSMS level

This is SSMS level settings and I kept this option always selected as I often need the column headers when I select the resultset.

Go Tools >> Options >> Query Results >> SQL Server >> Results to Grid >> Check the Box “Include column header when copying or saving the results.”

Both of the methods are discussed in following SQL in Sixty Seconds Video.

Here is the code used in the video.

Related Tips in SQL in Sixty Seconds:

If we like your idea we promise to share with you educational material.

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

SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet

No!

It is not a SQL Server Issue or SSMS issue. It is how things work. There is a simple trick to resolve this issue.

It is very common when users are coping the resultset to Excel, the floating point or decimals are missed.

The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that. Now as Zero which are training any digit after decimal points have no value, Excel automatically hides it. To prevent this to happen user has to convert columns to text format so it can preserve the formatting.

Here is how you can do it.

Select the corner between A and 1 and Right Click on it.

It will select complete spreadsheet. If you want to change the format of any column you can select an individual column the same way. In the menu Click on Format Cells…

It will bring up the following menu. Here by default the selected column will be General, change that to Text. It will change the format of all the cells to Text.

Now once again paste the values from SSMS to the Excel. This time it will preserve the decimal values from SSMS.

Solved!

Any other trick you do you know to preserve the decimal values? Leave a comment please.

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