How to create a function that will return numberOfPeriods between two dates

Can someone help with a SQL function that takes four parameters (StartDate, EndDate, and DayOftheWeek, and Frequency) and returns the numberofPeriods are between these date range.

For example, If you pass the startdate as 05/12/2011, enddate as 06/12/2011, and DayOftheweek = Friday, and Frequency = Weekly) then it should calculate how many fridays are in between 05/12/2011 and 06/12/2011 on a weekly basis. In this example, there are 5 fridays between these date range.

I was with you until

how many fridays are in between 05/12/2011 and 06/12/2011 on a weekly basis

I'm going to assume that phrase "on a weekly basis" is just noise. (It's literally nonsense, isn't it? What's the difference between the number of Fridays on a weekly basis, and the number of Fridays on a daily or monthly basis?)

The simplest solution is to use a calendar table. I think it's also the easiest to understand.

select count(*) 
from calendar
where (cal_date between '2011-05-12' and '2011-06-12')
  and (day_of_week = 'Fri');

I've posted a simplified version of the one I use. It's written for PostgreSQL, but it's easily adaptable. Pay attention to the constraints.

This will give you want with no new special tables. This is a sql server solution. You can craft that into a view or stored procedure for reuse

declare @beginDate date,@endDate date,@dayOfWeek int
set @beginDate = '05/12/2011'
set @endDate = '2011-06-12'
set @dayOfWeek = 6;

with datesBetween as (
select @beginDate as OriginalDate,0 as Periods
union all 
select  CAST(DATEADD(day,1,OriginalDate) as date), CASE When datepart(weekday,OriginalDate) = 6 then Periods+1 else Periods end
from datesBetween
where OriginalDate <> @endDate
)

select MAX(periods)
from datesBetween