Convert VBA Function to SQL Server UDF?

Hi there,
   I have a function that is executed quite often.  Now that we have our tables in SQL Server. I've been tasked to go back increase performance on the slower areas of the application.  I've found a slow area.  If I could convert the function below to a UDF then I could convert, rewrite, my VBA to be a stored procedure.  I'm not sure how to make this a UDF.  Instead of fumbling for hours to do it myself.  I'm putting it up here and have set the points to maximum.  Thanks in advance...Mike

Public Function AddWorkDays(ByVal StartDate As Date, ByVal NDays As Integer) As Date
' ***************************************************************************
' Add NDays of workdays to StartDate.
' Use: NewDay = WorkDay( "7/1/1999",20) Adds 20 Workdays days to 7/1/1999
' Work days is defined as weekdays that do not appear in a table tblHolidays
' The Table tblHolidays has one field called Holiday, defined as Date/Time,
' required, no duplicates, and being the primary key
' ***************************************************************************

    Dim rstTemp As ADODB.Recordset
    Set rstTemp = New ADODB.Recordset
    Dim D As Integer
    Dim DayIncrement As Date

    rstTemp.Open "tblHolidays", CurrentProject.Connection, adOpenStatic, adLockReadOnly
    DayIncrement = Int(StartDate) 'Make sure there’s no time part on the startdate

    Do Until D = NDays 'Top of the loop
        If NDays < 0 Then
            DayIncrement = DayIncrement - 1
        Else
            DayIncrement = DayIncrement + 1
        End If

        If Not ((DatePart("w", DayIncrement, vbSunday) = 1) Or (DatePart("w", DayIncrement, vbSunday) = 7)) Then
            rstTemp.Find "Holiday = #" & DayIncrement & "#" 'See if it’s a holiday
                If rstTemp.EOF Then 'If not a holiday, add one to D.
                    If NDays < 0 Then
                        D = D - 1
                    Else
                        D = D + 1
                    End If
                End If
                rstTemp.MoveFirst
        End If
    Loop

    rstTemp.Close
    AddWorkDays = DayIncrement
End Function
LVL 18
Data-ManCOOAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jdlambert1Connect With a Mentor Commented:
CREATE FUNCTION uf_AddWorkDays (@StartDate smalldatetime, @NDays int) RETURNS smalldatetime AS
BEGIN
  DECLARE @fDate smalldatetime, @WorkDayCounter int, @CalendarDays int, @CheckDate smalldatetime
  SELECT @WorkDayCounter = 0, @CalendarDays = 0, @CheckDate = @StartDate
  WHILE @WorkDayCounter <= @NDays
    BEGIN
      SET @CheckDate = DateAdd(d,1,@CheckDate)
      IF DatePart(dw,@CheckDate) NOT IN (1,7) AND NOT EXISTS (SELECT 1 FROM tblHolidays WHERE Holiday = @CheckDate)
      SET @WorkDayCounter = @WorkDayCounter + 1
      SET @CalendarDays = @CalendarDays + 1
    END
  SET @fDate = DateAdd(d,@CalendarDays,@StartDate)
  RETURN @fDate
END

-- Example to execute function
--SELECT dbo.uf_AddWorkDays('7/1/1999',20)
0
 
Partha MandayamTechnical DirectorCommented:
What is this doing?
 DayIncrement = Int(StartDate)

Won't it give an error since startdate is a date? It gives type mismatch!
0
 
Data-ManCOOAuthor Commented:
it just removes the time from the date passed in.

Since dates are stored as numbers, taking the int, removes the decimal, thus removing the time.

Mike
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Partha MandayamTechnical DirectorCommented:
If dates are stored as numbers then how can you pass Startdate as date in your function.
In your example you are passing the date as date, not a number!
Use: NewDay = WorkDay( "7/1/1999",20)
0
 
Data-ManCOOAuthor Commented:
the storing of dates as numbers has been this way for a number of versions...this is nothing new

in Acces in the Immediate Window do this

?cdbl(now())

Think of it this way

DayIncrement = int(cdbl(StartDate))


Mike




0
 
Data-ManCOOAuthor Commented:
JD...thanks...Let me test it

Mike
0
 
Data-ManCOOAuthor Commented:
you the man JD...thanks a bunch....I'll convert the VBA and use the UDF...should increase the performance by 10 fold.

By the way I did get that other Access for to work with the stored procedure...I just scaled back the fields to remove the ones from the left join....I put a 'more info' button the form to show them the missing info.

Mike
0
 
Partha MandayamTechnical DirectorCommented:
Then why does this give type mismatch

DayIncrement = Int("7/1/1999")

How does your code work?
0
 
Data-ManCOOAuthor Commented:
becuase it has to be treated as a date and not a string

?Int(#7/1/1999#)

Mike
0
 
jdlambert1Commented:
I detected an error in logic in the WHILE line when I tested using a value of 1 for the NDays. This should correct it:

CREATE FUNCTION uf_AddWorkDays (@StartDate smalldatetime, @NDays int) RETURNS smalldatetime AS
BEGIN
  DECLARE @fDate smalldatetime, @WorkDayCounter int, @CalendarDays int, @CheckDate smalldatetime
  SELECT @WorkDayCounter = 0, @CalendarDays = 0, @CheckDate = @StartDate
  WHILE @WorkDayCounter < @NDays
    BEGIN
      SET @CheckDate = DateAdd(d,1,@CheckDate)
      IF DatePart(dw,@CheckDate) NOT IN (1,7) AND NOT EXISTS (SELECT 1 FROM tblHolidays WHERE Holiday = @CheckDate)
      SET @WorkDayCounter = @WorkDayCounter + 1
      SET @CalendarDays = @CalendarDays + 1
    END
  SET @fDate = DateAdd(d,@CalendarDays,@StartDate)
  RETURN @fDate
END
0
 
Data-ManCOOAuthor Commented:
Thanks for taking the time to test the process...Most people wouldn't do that after the points are awarded.  I'm almost finished with the conversion....but has to help some users....ugggghhh

Mike
0
 
jdlambert1Commented:
I've got a developer friend who never rarely leaves his office because he gets accosted by so many users in the hallway, each one saying "this will only take a minute..."
0
 
Data-ManCOOAuthor Commented:
One small problem....it needs to work backwards as well...I tried this

SELECT dbo.uf_AddWorkDays('7/1/1999',-20) and it returns the same date passed in.

sorry about that...I should have tested it sooner, but I'm just now getting back to the task at hand.

mike
0
 
jdlambert1Commented:
I'm great at working backwards, but I'm an optimist so I don't think about negative things...

This should do it, but if someone passes a zero, it will still return the same date that's passed in (makes sense).

CREATE FUNCTION uf_AddWorkDays (@StartDate smalldatetime, @NDays int) RETURNS smalldatetime AS
BEGIN
  DECLARE @fDate smalldatetime, @WorkDayCounter int, @CalendarDays int, @CheckDate smalldatetime
  SELECT @WorkDayCounter = 0, @CalendarDays = 0, @CheckDate = @StartDate
  IF @NDays > 0
    BEGIN
      WHILE @WorkDayCounter < @NDays
        BEGIN
          SET @CheckDate = DateAdd(d,1,@CheckDate)
          IF DatePart(dw,@CheckDate) NOT IN (1,7) AND NOT EXISTS (SELECT 1 FROM tblHolidays WHERE Holiday = @CheckDate)
            SET @WorkDayCounter = @WorkDayCounter + 1
          SET @CalendarDays = @CalendarDays + 1
        END
    END
  ELSE
    BEGIN
      WHILE @WorkDayCounter > @NDays
        BEGIN
          SET @CheckDate = DateAdd(d,-1,@CheckDate)
          IF DatePart(dw,@CheckDate) NOT IN (1,7) AND NOT EXISTS (SELECT 1 FROM tblHolidays WHERE Holiday = @CheckDate)
            SET @WorkDayCounter = @WorkDayCounter - 1
          SET @CalendarDays = @CalendarDays - 1
        END
    END
  SET @fDate = DateAdd(d,@CalendarDays,@StartDate)
  RETURN @fDate
END
0
 
Data-ManCOOAuthor Commented:
I'm testing it now....Mike
0
 
Data-ManCOOAuthor Commented:
worked like a champ....

Here is how I used it...oh so fast

DECLARE @intDaysToSchedule INT

SET @intDaysToSchedule = IsNull((SELECT DaysConstructionStartDate from tblProjects WHERE Project = @strProject),90)

UPDATE tblProspects2Lots SET tblProspects2Lots.ConstructionStartDate = (SELECT dbo.uf_AddWorkDays(tblProspects2Lots.ScheduledCloseDate,-(@intDaysToSchedule - 1)))

FROM tblActionPlanParent app
      INNER JOIN (tblProspects2Projects p2p
      INNER JOIN tblProspects2Lots
            ON (p2p.ProspectID = tblProspects2Lots.ProspectID) AND (p2p.Project = tblProspects2Lots.Project))
            ON (p2p.Rank = app.ActionPlan) AND (app.Project = p2p.Project)

WHERE ((tblProspects2Lots.ScheduledCloseDate Is Not Null) AND (tblProspects2Lots.ActualCloseDate Is Null)
      AND (tblProspects2Lots.Project=@strProject) AND (app.BuyStatus='Non-Contingent' Or app.BuyStatus='Closed')
      AND (tblProspects2Lots.InactiveRecord=0))

Thanks again...Mike
0
 
andy1892Commented:
Great bit of Code - thanks to all
0
 
Data-ManCOOAuthor Commented:
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.