March 19, 2011 at 2:13 pm
I'm evaluating a process that mails statements and letters and has a service level agreement, and therefore have to find if a certain job went out on time. I have been able to complete everything except one item, how to figure out if a postal holiday is between the start and finish dates.
The following code does work:
ALTER FUNCTION [dbo].[Holiday_Find] (@start_date smalldatetime, @end_date smalldatetime)
RETURNS INT
AS
BEGIN
Declare @X_Hol As Integer
SET @X_Hol = '0'
if '09/06/2010' between @start_date and @end_date
Set @X_Hol = '1'
RETURN @X_Hol
END
Problem I have is modifying the IF statement. I would like to have all the date data stored in a table, that way the table can be modified, but not the coding itself. The function at that point can evaluate the dates in the table.
Any help would be appreciated!
March 19, 2011 at 4:44 pm
Assuming it is the holiday dates that you want to store in a table, this gives you the number of holidays between the two dates:select @x_hol =isnull(count(*),0) from YourHolidayTable
where holiday_date between @start_date and @end_date;
March 19, 2011 at 11:24 pm
This works beautifully! Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply