About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Wednesday, January 22, 2014

TSQL Function: Count Work Days from two dates.

A T-SQL function to calculate number of business days (work days) given two dates.
It uses CTE recursive to quickly get a row number count.

CREATE FUNCTION [hrr].[usf_GetWeekdayCount]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
BEGIN

IF(@StartDate IS NULL)
  RETURN 1

IF(@EndDate IS NULL)
  RETURN 1

DECLARE @FlipBitFlag bit
SET @FlipBitFlag = 0

IF(@StartDate > @EndDate)
BEGIN   DECLARE @Temp datetime
   SET @Temp = @StartDate
   SET @StartDate = @EndDate
   SET @EndDate = @Temp
   SET @FlipBitFlag = 1
END
DECLARE @WEEKDAYS_DAY AS INT
 
---Count up to 2^16 = 65536 real fast!!
;WITH N1 (n) AS
(
 
 
SELECT 1

 
UNION ALL 
  
SELECT 1
),
N2 (n) AS
(
 
 
SELECT 1 FROM N1 AS X, N1 AS Y
),
N3 (n) AS
(
 
 
SELECT 1 FROM N2 AS X, N2 AS Y
),
N4 (n) AS
(
 
 
SELECT 1 FROM N3 AS X, N3 AS Y
),
N5 (n) AS
(
 
 
SELECT ROW_NUMBER() OVER(ORDER BY X.n) FROM N4 AS X, N4 AS Y
)
 
 


SELECT @WEEKDAYS_DAY=SUM(1)
FROM N5
WHERE DATEADD(day,n-1,@startdate)<=@enddate
AND DATENAME(dw,DATEADD(day,n-1,@startdate)) NOT IN ('SATURDAY','SUNDAY')

IF(@FlipBitFlag = 1)
  RETURN -1 * @WEEKDAYS_DAY


RETURN @WEEKDAYS_DAY

END

GO

--EDITED: Added check for nulls and reverse dates will give negative value.

1 comment:

Glen Pinheiro said...

interesting article, please check out my blog, http://code-clues.blogspot.in/, it has got one article with guidelines on creating quality web applications, & others.