VB function to SQL UDFunction - Working Days
-
Hey guys, Looking for some help in converting a access module into a User defined function in SQL. What it does is work out the working days between 2 dates. I have done most of it (I THINK!!) but i am having trouble setting the datatypes of the variables.
Here is the original version:
Attribute VB_Name = "WorkDays"
Option Compare Database
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
On Error GoTo Err_Work_Days
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
Exit Function
Err_Work_Days:
' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates. '
If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
End If
End Function
And here is my version that i think i have converted:
CREATE Function Work_Days (@BegDate As nvarchar, @EndDate As numeric) RETURNS INTEGER as
begin
DECLARE @WholeWeeks As integer
DECLARE @DateCnt As varchar
DECLARE @EndDays As IntEGER
set @WholeWeeks = DateDiff("w", @BegDate, @EndDate)
set @DateCnt = DateAdd("ww", @WholeWeeks, @BegDate)
set @EndDays= 0
While @DateCnt < @EndDate
continue
If DATENAME(dw,@DateCnt) <> 'Sunday' And DATENAME(dw,@DateCnt) <> 'Saturday'
Begin
set @EndDays = @EndDays + 1
End
else
Begin
set @DateCnt = DateAdd("d", 1, @DateCnt)
End
return @WholeWeeks * 5 + @EndDays
End
Any help would be great guys. you can email me on slancaster@gates.com or msn slancaster81@hotmail.com
Thanks! and hopefully ill be able to help you out one day.
Scotty
-
Actually it would make sense, among other things, if you would use the DATETIME data type for @BeginDate and @EndDate.
And probably even more easier would it be to do a search in the script section here or on the web. You will surely find such a function already existed. So there is no need to reinvent the wheel.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hello Scott,
SQL Server has a data type called DATETIME that should be right for you.
In addition, I've had some trouble with the loop that tends to become infinite.
It should be something like this:
CREATE Function Work_Days (@BegDate As datetime, @EndDate As datetime) RETURNS INTEGER as
begin
DECLARE @WholeWeeks As integer
DECLARE @DateCnt As datetime
DECLARE @EndDays As IntEGER
set @WholeWeeks = DateDiff("ww", @BegDate, @EndDate)
set @DateCnt = DateAdd("ww", @WholeWeeks, @BegDate)
set @EndDays= 0
While @DateCnt < @EndDate
begin
If DATENAME(dw,@DateCnt) <> 'Sunday' And DATENAME(dw,@DateCnt) <> 'Saturday'
Begin
set @EndDays = @EndDays + 1
End
set @DateCnt = DateAdd("d", 1, @DateCnt)
End
return @WholeWeeks * 5 + @EndDays
End
There's still a problem if the start and end date are in the same week, but I'm running out of time right now.
I can offer you a similar function I've written some time ago. It also offers the possibility to decide if Saturdays should be counted as workdays or not. In addition, it should also work on a server that is not installed in English (which is unfortunately true for me), as long as Microsoft didn't decide to change the result of DATEPART(dw, ...) depending on the country. I hope the comments will help you understand what the function is doing.
create FUNCTION dbo.fnCalWorkDays
(
@ParStartDate datetime -- Date to start with (included)
,@ParEndDate datetime -- Date to end with (included)
,@ParCountSaturdays bit = 0 -- 1: Saturdays are workdays, 0: Saturdays are free (default)
)
RETURNS int -- Number of working days
AS
BEGIN
-- Calculate the number of working days between two days (including or excluding saturdays)
DECLARE @CountDays int
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @WorkDays int
-- Switch the dates if necessary
IF @ParStartDate < @ParEndDate
BEGIN
SET @StartDate = @ParStartDate
SET @EndDate = @ParEndDate
END
ELSE
BEGIN
SET @StartDate = @ParEndDate
SET @EndDate = @ParStartDate
END
IF (@ParCountSaturdays = 1)
BEGIN
-- Saturdays are working days
SET @WorkDays = 6
END
ELSE
BEGIN
-- Saturdays are free
SET @Workdays = 5
END
-- Calculate full weeks times working days per week
SELECT @CountDays = FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 7) * @Workdays
IF (DATEPART(dw, @StartDate) > DATEPART(dw, @EndDate))
BEGIN
-- Add days from start date to end of week (friday or saturday)
-- and from beginning of week to end date
SELECT @CountDays = @CountDays + (@Workdays + 2 - DATEPART(dw, @StartDate)) + (DATEPART(dw, @EndDate) - 1)
END
IF (DATEPART(dw, @StartDate) < DATEPART(dw, @EndDate))
BEGIN
-- Add days between start weekday and end weekday
-- subtract 1 if starting day is sunday
-- subtract 1 if ending day is saturday and saturday is free
SELECT @CountDays = @CountDays + (DATEPART(dw, @EndDate) - DATEPART(dw, @StartDate) + 1)
IF (DATEPART(dw, @StartDate) = 1)
BEGIN
SELECT @CountDays = @CountDays - 1
END
IF ((@ParCountSaturdays = 0) AND (DATEPART(dw, @EndDate) = 7))
BEGIN
SELECT @CountDays = @CountDays - 1
END
END
RETURN @CountDays
END
I hope I was able to help you.
Christian
-
Thanks Christian and Frank for your answers.
Christian i used both the one you fixed up for me.. thought their might be a problem with the loop statement. And i also compared it to the one you supplied. Both return different values . i used your saying that saturdays were not included. either way, ill have a better look into the script to see if i can work out why it is doing it.
date enddate Scotts Christians
1/01/2004 10/01/2004 7 8
1/01/2004 4/10/2004 200 198
12/01/2004 16/01/2004 4 4
This was the first time i tried creating a UDf so thanks for all you inputs.. Im sure you will see me back here soon enough.
thanks again
Scotty
-
There is a set based way to do this, but first you need to set up a common SQL table called "Sequence":
CREATE TABLE [Sequence] (
[SequenceID] [int] IDENTITY (1, 1) NOT NULL ,
PRIMARY KEY CLUSTERED
(
[SequenceID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
declare @SequenceID Int
While isnull(@SequenceID, 0) <=1000
BEGIN
Insert Sequence default values
select @SequenceID = @@identity
END
Now you can create the function:
create function UTIL_WorkingDaysDiff(@StartDate datetime, @EndDate datetime)
returns int
as
begin
declare @Count int
--check for null values
if @StartDate is null or @EndDate is null
goto fEND
---check that dates aren't the same
if convert(varchar(12), @StartDate, 101) = convert(varchar(12), @EndDate, 101)
BEGIN
Select @Count = 0
goto fEND
END
--check that startdate < endDate
if @StartDate > @EndDate
goto fEND
select @Count =
sum (
Case datepart(weekday, dateadd(dd, SequenceID, @StartDate))
When 1 then 0
When 7 then 0
Else 1
END
 
From Sequence
where SequenceID <= datediff(dd, @StartDate, @EndDate)
fEND:
return @Count
END
If you are worried about date ranges greater than the number of records you would want to check that the datediff(@StartDate, @EndDate) is less then the max(SequenceID), and if it isn't add more records to the Sequence table.
Signature is NULL
-
Hello Scott,
this is really weird... I've tried on different servers, but I get different results using my function than you do:
select dbo.fncalworkdays(convert(datetime,'01.01.2004', 104), convert(datetime,'10.01.2004', 104), 0) --> returns 7
select dbo.fncalworkdays(convert(datetime,'01.01.2004', 104), convert(datetime,'04.10.2004', 104), 0) --> return 198
select dbo.fncalworkdays(convert(datetime,'12.01.2004', 104), convert(datetime,'16.01.2004', 104), 0) --> returns 5
Sorry, I'm using German date formats, I'm just used to converting dates this way.
The only thing I could think of is the DATEPART() function, which should return 7 for Saturday and 1 for Sunday. This can be influenced by SET DATEFIRST. The default for English is 7 (Sunday is the first day of the week) and that is the setting we are using.
Maybe your machine is set to DATEFIRST 1 (Monday is the first day of the week), which will lead to a different result.
You might simply try to run SET DATEFIRST 7 before calling the function. As far as I know, this should only influence the current session.
Of course, you can as well change all DATEPART() calls in the function to fit your setting of DATEFIRST.
SELECT @@DATEFIRST will show your current setting.
Christian
-
There's actually a script by Ed Cardin featured in today's email:
http://qa.sqlservercentral.com/scripts/contributions/1310.asp
This is definitely a valid option, and provides a way of specifying whether a day is a holiday or not, quarters, etc. I use something similar in my OLAP cubes for date dimensions...
Still, I like my sequence table approach and the fact that it's set based.
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply