This is the eighth article in a short series of SQL Server query challenges to test and expand your SQL Server querying skills. While none of these short articles is of overwhelming complexity, they will hopefully either teach you something new or help you to reactivate potentially dormant SQL skills in preparation for your next real-world challenge. You can see the rest of the articles on the Query Answers with SQL Server page.

In this article we will once again imagine that you are working for a small classic car retailer called Prestige Cars Ltd, using the data that is in the company database.

The Challenge

As a prelude to delivering some more complex analysis of staff deployment, the HR department wants to know how many weekend days there are in March and April 2018. Your challenge is to come up with the SQL to satisfy their request. Here is one possible solution:

;
WITH TallyTable_CTE
AS
(
SELECT     ROW_NUMBER() OVER (ORDER BY StockCode) - 1 AS ID
FROM       Data.Stock
)
,WeekendList_CTE
AS
(
SELECT        CAST(DATEADD(DD, ID, '20180301') AS DATE) AS WeekdayDate
FROM         TallyTable_CTE
WHERE        DATEPART(dw, DATEADD(DD, ID, '20180301')) IN (1,7)
             AND ID <= DATEDIFF(DD, '20180301', '20180430')
)
SELECT    COUNT(*) AS WeekendDays FROM WeekendList_CTE

When you run this query, you should see the output that is shown in Figure 1.

Figure 1. Counting the number of non-weekdays for a date range

How It Works

What this query does is create a table of all the days between two dates ( March 1 and April 30, 2017, in this example). It then deduces the number of the day for each date and excludes Saturdays and Sundays. Finally, it counts the remaining number of records in the list.

The code works like this:

First:

A tally CTE is created to provide a sequential list of numbers starting with 0 (zero). TAlly CTEs are explained in a previous article in this series.

Then:

A second CTE uses the tally list as part of a DATEADD() function to create a list of dates. This list begins with 0 to give the starting date —by adding zero days to the lower date threshold that is the third parameter of the DATEADD() function—and then continues adding one day for each record in the tally list. The WHERE clause of the second CTE filters out any weekdays by

Reusing the same DATEADD() expression and enclosing this in a DATEPART() function that returns the number of the weekday. The filter only allows days 1 and 7 (Saturdays and Sundays) to appear in the second CTE output.

Limiting the number of records from the tally list that are used. It does this by creating a DATEDIFF() function that returns the number of days between the lower and upper threshold dates. This number of days is the number of records that the subquery should return. Consequently, the maximum number of records in the tally list is set by filtering on the maximum ID value for the second CTE output.

Finally:

A simple SELECT clause using the data from the second CTE counts the number of weekend days between the two dates.

Tricks and Traps

I have a couple of points to make here:

• Once again, you may have to repeat the date thresholds several times in queries like this one. The essential thing is to be careful and consider whether you should be using the lower or upper date thresholds in each case.
• Date tally lists are often fairly short; however, remember that you can make quite large tally lists if you need them by using the same table name two or even three times. This approach gives you code like this:

SELECT     ROW_NUMBER() OVER (ORDER BY Y.StockCode) - 1 AS ID
FROM       Data.Stock Y
CROSS JOIN Data.Stock Z

That is it – you have seen a simple example of how to count the number of weekend days between two dates. Keep watching SQL Server Central.com ready for the next article in this series.

This article is adapted from the book “Query Answers with SQL Server Volume II: In-Depth Querying” by Adam and Karine Aspin. Available as both a paperback and an eBook from all good resellers including Amazon, Kobo and iBooks, as well as all good bookstores.

The sample data is available either in the accompanying material for this article, or on the Tetras Publishing website at: www.tetraspublishing.com/sqlserver.

The Series

There are a number of articles in this series. You can see them all on the Query Answers page.