[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert VBA Function to SQL Server UDF?

Posted on 2004-09-23
18
Medium Priority
?
1,209 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Data-Man
  • 10
  • 4
  • 3
  • +1
18 Comments
 
LVL 6

Expert Comment

by:mcp111
ID: 12136584
What is this doing?
 DayIncrement = Int(StartDate)

Won't it give an error since startdate is a date? It gives type mismatch!
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12136610
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
 
LVL 6

Expert Comment

by:mcp111
ID: 12136687
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
jdlambert1 earned 2000 total points
ID: 12136822
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
 
LVL 18

Author Comment

by:Data-Man
ID: 12136823
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
 
LVL 18

Author Comment

by:Data-Man
ID: 12136847
JD...thanks...Let me test it

Mike
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12136863
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
 
LVL 6

Expert Comment

by:mcp111
ID: 12136906
Then why does this give type mismatch

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

How does your code work?
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12136930
becuase it has to be treated as a date and not a string

?Int(#7/1/1999#)

Mike
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12137313
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
 
LVL 18

Author Comment

by:Data-Man
ID: 12137419
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12137450
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
 
LVL 18

Author Comment

by:Data-Man
ID: 12138725
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12138856
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
 
LVL 18

Author Comment

by:Data-Man
ID: 12138895
I'm testing it now....Mike
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12138939
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
 

Expert Comment

by:andy1892
ID: 27596320
Great bit of Code - thanks to all
0
 
LVL 18

Author Comment

by:Data-Man
ID: 27596457
:-)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question