How many occurrences of a group of week days occurs in a date range?

This week's UDF is a reader contribution. The function is udf_CompleteWeekRange, which calculates the number of weeks in a date range that contain a complete set of a group of days. The group of days might be Monday-Friday, or Tues-Wednesday. The number of complete ranges between two dates will vary depending on the date range that you request.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Function dbo.udf_CompleteWeekRange (
    @StartDate Datetime
   ,@EndDate Datetime
   ,@DayNumber1 int
   ,@DayNumber2 int)
-- CompleteWeekRange(StartDate,EndDate,DayNumber1,DayNumber2) 
--             1=Sun,2=Mon,3=Tue,4=Wed,5=Thu,6=Fri,7=Sat
Returns Int
As Begin 
   Declare @nCnt Money
   Set @nCnt=0
   While @StartDate <= @EndDate
   Begin
      Set @nCnt=@nCnt 
            + case when datepart(dw,@StartDate)=@DayNumber1
                                  then .5 else 0 end
      Set @nCnt=@nCnt 
            + case when datepart(dw,@StartDate)=@DayNumber2 
                        and @nCnt>Floor(@nCnt) then .5 else 0 end
      Set @StartDate=@StartDate+1
   End
   Return Floor(@nCnt)
End
GO


GRANT EXEC on dbo.udf_CompleteWeekRange TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Here are a few examples that show the number of full week ends and the number of full weeks in April and May of this year:

select dbo.udf_CompleteWeekRange('2004-04-01', '2004-04-30'
                                , 7, 1) [Wkends in Apr]
     , dbo.udf_CompleteWeekRange('2004-04-01', '2004-04-30'
                                , 2, 6) [Wrk Wks in Apr]
     , dbo.udf_CompleteWeekRange('2004-05-01', '2004-05-31'
                                , 7, 1) [Wkends in May]
     , dbo.udf_CompleteWeekRange('2004-05-01', '2004-05-31'
                                , 2, 6) [Work Wks in May]GO

Wkends in Apr Wrk Wks in Apr Wkends in May Work Wks in May 
------------- -------------- ------------- --------------- 
            4              4             5               4 

Thanks to our readers for their code contributions

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick