Get Work Days Count

Introduction

I will let the code speak for itself.

Using the Code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO

-- =============================================
-- Author: Tarek Najem
-- =============================================
CREATE FUNCTION [dbo].[GetDaysCount]
(
    @FromActionDate DATE
    ,@ToActionDate DATE
    ,@Equality BIT
    ,@Saturday BIT
    ,@Sunday BIT
    ,@Monday BIT
    ,@Tuesday BIT
    ,@Wednesday BIT
    ,@Thursday BIT
    ,@Friday BIT
)
RETURNS INT 
AS 
BEGIN

DECLARE @Range INT; 
DECLARE @DayFactor INT;

    SET @DayFactor =
        (CASE WHEN @Equality != @Saturday THEN 1 ELSE 0 END)
        + (CASE WHEN @Equality != @Sunday THEN 1 ELSE 0 END)
        + (CASE WHEN @Equality != @Monday THEN 1 ELSE 0 END)
        + (CASE WHEN @Equality != @Tuesday THEN 1 ELSE 0 END)
        + (CASE WHEN @Equality != @Wednesday THEN 1 ELSE 0 END)
        + (CASE WHEN @Equality != @Thursday THEN 1 ELSE 0 END)
        + (CASE WHEN @Equality != @Friday THEN 1 ELSE 0 END)

    SET @Range = DATEDIFF(DAY, @FromActionDate, @ToActionDate) + 1; 

RETURN 
(
    SELECT 
        ((@Range / 7) * @DayFactor) + (@Range % 7) - 

        ( 
            SELECT
                COUNT(*) 
            FROM 
            ( 
                SELECT 1 AS [Day]
                UNION ALL SELECT 2 
                UNION ALL SELECT 3 
                UNION ALL SELECT 4 
                UNION ALL SELECT 5 
                UNION ALL SELECT 6 
                UNION ALL SELECT 7 
            ) WeekDays 
            WHERE
                ([Day] <= (@Range % 7))
                AND (DATENAME(WEEKDAY, DATEADD(DAY, -([Day] + 1), @ToActionDate)))
                IN 
                ( 
                    (CASE WHEN @Equality =  _
			@Saturday THEN DATENAME(WEEKDAY, '1753-1-6') _
			ELSE NULL END) --'Saturday'
                    ,(CASE WHEN @Equality = @Sunday THEN DATENAME(WEEKDAY, '1753-1-7') _
			ELSE NULL END) --'Sunday'
                    ,(CASE WHEN @Equality = @Monday THEN DATENAME(WEEKDAY, '1753-1-8') _
			ELSE NULL END) --'Monday'
                    ,(CASE WHEN @Equality = @Tuesday THEN DATENAME(WEEKDAY, '1753-1-9') _
			ELSE NULL END) --'Tuesday'
                    ,(CASE WHEN @Equality = _
			@Wednesday THEN DATENAME(WEEKDAY, '1753-1-10')_
			 ELSE NULL END) --'Wednesday'
                    ,(CASE WHEN @Equality = _
			@Thursday THEN DATENAME(WEEKDAY, '1753-1-11') _
			ELSE NULL END) --'Thursday'
                    ,(CASE WHEN @Equality = @Friday THEN DATENAME(WEEKDAY, '1753-1-12') _
			ELSE NULL END) --'Friday'
                )
        )
    )

END        

To use:

DECLARE @FromActionDate DATE = '2010-06-01'
DECLARE @ToActionDate DATE = '2010-06-30'
DECLARE @Equality  BIT  = 0
DECLARE @Saturday  BIT  = 1
DECLARE @Sunday   BIT  = 1 
DECLARE @Monday   BIT  = 1 
DECLARE @Tuesday  BIT  = 1 
DECLARE @Wednesday  BIT  = 1 
DECLARE @Thursday  BIT  = 1 
DECLARE @Friday   BIT  = 0
SELECT [dbo].[GetDaysCount]
      (
        @FromActionDate
       ,@ToActionDate
       ,@Equality
       ,@Saturday
       ,@Sunday
       ,@Monday
       ,@Tuesday
       ,@Wednesday
       ,@Thursday
       ,@Friday
      )
SET @Equality = 1
SELECT [dbo].[GetDaysCount]
      (
        @FromActionDate
       ,@ToActionDate
       ,@Equality
       ,@Saturday
       ,@Sunday
       ,@Monday
       ,@Tuesday
       ,@Wednesday
       ,@Thursday
       ,@Friday
      )
-- Result [1] Is:  26 || Result [2] Is:  4

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)