SSRS issue using sql udf

Question

SSRS issue using sql udf

ryanpettet

Valued Member

Points: 65
More actions
April 13, 2011 at 5:07 am

#234699

Hi all,
I have written a udf to calculate workings days from a date range. Very simple function that excludes weekends and public holiday using a lookup table.
Function:
ALTER function [dbo].[udf_Calculate_Working_Days2]
(
@Date_Start smalldatetime
,@Date_End smalldatetime
)
returns int
as
begin
declare @IsHoliday int
declare @Working_Days int
--Debugs
--declare @iday int
--declare @sday nvarchar(20)
--Add 1 day to date range
set @Date_End = dateadd(d,1,@Date_End)
set @Date_Start = cast('2011-MAR-01' as smalldatetime)
set @Date_End = cast('2011-APR-02' as smalldatetime)
set @Working_Days = 0
/* Program logic */
while @Date_Start < @Date_End
begin
--set @sday = DATENAME(dw, @Date_Start)
--set @iday = datepart(dw,@Date_Start)
if (datepart(dw,@Date_Start) < 6) --and (datepart(dw,@Date_Start) <> 7)) --Is not a weekend
begin --Is not a weekend, Check if a public holiday
/* check if public holiday */
set @IsHoliday = null
select @IsHoliday = coalesce(Entry_ID,0)
from Bank_Holidays
where CONVERT(CHAR(10),[Holiday_Date],120) = CONVERT(CHAR(10),@Date_Start,120)
if(@IsHoliday IS NULL) -- Working Day
begin
set @Working_Days = @Working_Days + 1
end
end
--Shift Day on 1
set @Date_Start = DateAdd(d,1, @Date_Start)
end
return @Working_Days /* return value back to calling statement */
end
GO
I have set the date values in the function for debugging purposes. When I execute the function in sql studio, I get 24 for the date range and this is what I am expecting.
When I use the function in a stored procedure and generate a report in SRSS, it returns 23. I initially thought I had a date formatting issue, being in the UK and I might still have that problem, but for the same function to return different values is not making sense. Especially since I have hard coded the date values.
I have even gone as far to create a dataset in SSRS using the same test sql I used in studio and again studio returns 24 and SSRS report says 23.
Any help or pointers would be much appreciated.
Thank you
Ryan

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

When you say SSRS, do you mean Visual Studio? If so, refresh your report in Preview mode. In order to render a report faster VS will frequently display old data unless you refresh the screen.

ryanpettet Valued Member Points: 65 More actions · Answer 2

Hi,

Thanks for your reply. It gives the same result in SSRS and VS. I even shut down VS and reloaded the project. I do know VS does not always refresh the preview.

It is genuinely returning 23 instead of 24. My first thought was I have lost my mind. Second more calm thought is, what am I missing? 🙂

I even deleted the original function and recreated it. I did a test dataset in the report to compare the result direct from the function and it returned 23.

Thanks

Ran

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Well for 1 I think your function is way too complexe for no good reason.

I also use a calendar table. I have all dates between 2000 and 2040, including holidays.

I also have a calculated column that outputs 1 for weekdays that are not holidays and 0 for the rest.

Then all I need to do is select sum(IsBusDay) FROM dbo.Calendar WHERE dt BETWEEN @Start AND @End

Can also easily be turned to inline table function so that I can use in cross apply operations... very fast and very simple, and very easy to debug!

ryanpettet Valued Member Points: 65 More actions · Answer 4

Thanks for the input.

I did the table solution and created the dates till 2040 and updated the table using my public holidays table.

Works a treat.

Ryan