Determining if a recurring date matches a certain date

[keb1965]

We want to implement the ability for employees to ask for recurring days off, whether it is once a week, every two weeks or a certain day of a certain week of the month. For example, an employee may request the last Thurday of the month, every month, into perpetuity. (not that they will be granted)

Anyway, I have a table design that looks like this:
intStaffID - Staff ID FK to tblStaff.ID
intFrequency - 1=weekly, 2=monthly
intDay - 1-7=Day of week, 1-31=Day of month
intInterval - every nth intFrequency
intWeek - Week of the month 1-4 = 1st-5th week, 6=last week
intMonth - 1-12
dtStart
dtEnd

Because some months do not have a 5th occurence of a day, if I attempt to calculate a 5th of nth day, I will be pushed into the next month, throwing off all of the remaining days, so I need to know in advance if the test day is in the 5th week or alternatively if the setting is to use the last week of the month.

Here is where I am stuck.

	--Set the time part to 0
	SELECT @CompareDate = DATEADD(DAY, DATEDIFF(DAY, 0, @CompareDate), 0)

	--Increment the starting date to the day of the week we are looking for Maximum 6 iterations
	WHILE NOT (DATEPART(WEEKDAY,@StartDate) = @WeekDay)
		BEGIN
			SELECT @StartDate = DATEADD(DAY,1,@StartDate)
		END
	--Increment the starting week one week at a time until we reach the compare date
             --There has to be a more efficient way
	WHILE (@StartDate < @CompareDate)
		BEGIN
			SELECT @StartDate = DATEADD(WEEK,@Frequency,@StartDate)
		END

	IF (DATEDIFF(DAY, @StartDate, @CompareDate) = 0)
		BEGIN
			RETURN 1
		END
	
	RETURN  0
	END

Ideas?

 

[jmcilhinney]

This strikes me as a calculation that I'd be doing in the application rather than in the database.

 

[keb1965]

Well, I would do it in the application, but I fear it will actually be more complicated in the long run.

Here is what is happening:
We have a database with hundreds of employees and dozens of available shifts. When we retrieve the employees from the database, we only want those who can work on the specified day, at the specified time. So, while it would be possible to loop through a datatable of several hundred employees calculating the employee's days off and then comparing them to the specified day and time, I figured it might be easier to let the application simply display the data and let the database serve it up as needed.

The above code works ... sort of ... unless the staff has the last <insert day here> off every month or the 5th reoccuring day of the month. Some months that is the 4th occurence of the day, and some months it is the 5th occurence. Stepping up by 5 weeks obviously causes the day to be miscalculated because it is in the first week of the next month.

I'll continue to examine ways to manage it, but I suspect the best way might be to simply increment 5 weeks at a time for 5th reoccuring IF doing so doesn't push the date into the following month, then differentiate between last week of the month and a day in the 5th week of the month (for example, an organization I am a member of has a special meeting on the 5th Tuesday of the month IF there is a 5th Tuesday)