GregHowley.com

Even though I've been writing code professionally for over ten years and as a hobby for nearly thirty, my SQL experience had been rather minimal before this year. Select, insert, update, inner and outer join, union, group by, simple subqueries, and other basic stuff. I'd never worked with cursors, CTEs, or SQL recursion. So after having written this scalar function to return the number of weekdays that have passed since a given date, I'm plenty pleased with myself. I'm posting it here in the hopes that someone else will search the interwebs, stumble across this post, and in doing so save themselves some time.

-- =============================================
-- Author:		Greg Howley
-- Description:	Allows you to pass in a date and return the number of weekdays that have passed since then
-- =============================================
CREATE FUNCTION [dbo].[GetWeekdaysPassed] 
(
	@inputDate SmallDateTime
)
RETURNS int
AS
BEGIN
	DECLARE @CurrentDate SmallDateTime;
	SET @CurrentDate = DateAdd(dd,0, DateDiff(dd,0, GetDate()));

	RETURN
	(
	SELECT SUM(IsWeekDay) as WeekdaysAgo
	FROM
	(
		SELECT nums.SeqValue as DaysAgo, 
			DATEADD(dd, nums.SeqValue * -1, @CurrentDate) as TheDate,
			(CASE WHEN DatePart(dw,DATEADD(dd, nums.SeqValue * -1, @CurrentDate)) IN (2,3,4,5,6) THEN 1 ELSE 0 END) as IsWeekDay
		FROM
		(
			--This crazy query just grabs a list of numbers from 0 to 127
			SELECT
				(TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue + TWO_64.SeqValue) SeqValue
			FROM
				(SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
				CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
				CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
				CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
				CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
				CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
				CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 64 SeqValue) TWO_64
			--end crazy query 'nums'
		) nums
	) a
	WHERE a.TheDate > @inputDate
	)
END

So what's going on here? Let me break it down. The crazy part is the inner query, which I found here. I can't claim credit for engineering that bit. In the end, all it does is spit out a list of numbers from 0 to 127.

Using that number list, my inner query is grabbing three things. Firstly, nums.SeqValue as DaysAgo, which is really just the list of numbers from 0 to 127. Alongside that, I'm selecting DATEADD(dd, nums.SeqValue * -1, @CurrentDate) as TheDate, which for each row is taking today's date and subtracting a number of days from it equal to the number in the first column. So alongside the 0, we'll get today's date. Alongside the 1, we'll get yesterday, et cetera. Lastly, we see (CASE WHEN DatePart(dw,DATEADD(dd, nums.SeqValue * -1, @CurrentDate)) IN (2,3,4,5,6) THEN 1 ELSE 0 END) as IsWeekDay. You can probably see that the inner part of that is identical to "TheDate". Here, we're checking whether the date in this row is a weekday. If it's a weekday, the value in this column will be a 1, otherwise a 0. Yes, this is just a column of zeroes and ones. You can see the results of this inner query in the image I've included.

From here, it's not difficult to select a SUM of the IsWeekDay column where the TheDate column is less than the date provided for the function. It works well.