0

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.

slugster
  • 49,403
  • 14
  • 95
  • 145
user749448
  • 189
  • 1
  • 5
  • 9
  • It seems to me like you're trying to use a Database Engine to calculate datespans.... it's certainly capable of this but I don't see why you would want to do that? Considering is has nothing to do with stored data.... – Matthew May 11 '11 at 22:01
  • 4
    What is "Frequency" about? If you pass DayOftheweek = Friday then what other frequency than Weekly would make sense? – Martin Smith May 11 '11 at 22:03

2 Answers2

1

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.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
1

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
JStead
  • 1,710
  • 11
  • 12