Solved

Convert VBA Function to SQL Server UDF?

Posted on 2004-09-23
18
1,166 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
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now