Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Red face Unanswered: new to sql - trying to convert vb function to udf

    Hi

    I'm an Access/VB coder by experience and trying to move apps to SQL Server 2000.
    I have got to grips with the basics of DTS and tables and views and Stored Procedures (to an extent) but now need to upgrade an app that uses a vb function to produce a phased value for a set of budgets.

    The VB function looks like this...



    ---------------------------------
    Function calcPercentOfBudget(datFromDate As Date, datToDate As Date, iMonth As Integer, cBudget As Currency) As Currency

    Dim iDuration As Integer
    iDuration = DateDiff("d", datFromDate, datToDate) + 1

    ' if either date not in current year then 0 (both dates should be in same year)
    If Year(datFromDate) <> Year(Now) Or Year(datToDate) <> Year(Now) Then
    calcPercentOfBudget = 0
    End If

    Dim sRatio As Single, idaysInMonth As Integer, idaysInYear As Integer

    ' if passed month outside of period then 0
    If Not (iMonth >= Month(datFromDate) And iMonth <= Month(datToDate)) Then
    calcPercentOfBudget = 0
    Exit Function
    End If

    idaysInMonth = daysInMonth(iMonth, Year(Now))


    'if from date and to date in same month then 100% of budget
    If Month(datFromDate) = Month(datToDate) Then
    calcPercentOfBudget = cBudget
    Exit Function
    End If

    ' if passed month in From month then ratio of passed month (caters for 1st day of month - 100%)
    If Month(datFromDate) = iMonth Then
    'calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / idaysInYear * cBudget
    calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / iDuration * cBudget
    Exit Function
    End If

    'if passed month in To month then ratio of passed month (caters for last day of month - 100%)
    If Month(datToDate) = iMonth Then
    'calcPercentOfBudget = Day(datToDate) / idaysInYear * cBudget
    calcPercentOfBudget = Day(datToDate) / iDuration * cBudget
    Exit Function
    End If

    ' if passed month within period then 100%
    If iMonth > Month(datFromDate) And iMonth < Month(datToDate) Then
    'calcPercentOfBudget = idaysInMonth / idaysInYear * cBudget
    calcPercentOfBudget = idaysInMonth / iDuration * cBudget
    Exit Function
    End If

    End Function
    ---------------------------------
    Function daysInMonth(iMonth As Integer, iYear As Integer) As Integer

    Dim datTemp As Date

    datTemp = CDate("1/" & CStr(iMonth) & "/" & CStr(iYear))
    datTemp = DateAdd("m", 1, datTemp)
    datTemp = DateAdd("d", -1, datTemp)

    daysInMonth = Day(datTemp)

    End Function

    ---------------------------------


    I have a UDF function LastDayInMonth to replace the daysInMonth vb function, and that works fine.

    However I'm starting to get frustrated in trying to convert the main VB funciton to a UDF function. This is what I have got to, and as you'll see its totally wrong!...



    CREATE FUNCTION [dbo].[calcPercentOfBudget] (@datFrom as datetime, @datTo as datetime , @iMonth as int, @cBudget as money, @GetDate as datetime)
    RETURNS money AS
    BEGIN
    declare @iDuration int
    declare @returnvalue money
    declare @sRatio decimal
    declare @iDaysInMonth int

    set @iDuration = datediff(d, @datFrom, @datTo)
    set @iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@iMonth as varchar) + '/' + cast(year(@getdate) as varchar))

    case
    when year(@datFrom) <> year(@getdate) or year(@datTo) <> year(@getdate)
    set @returnvalue = 0
    when not(@iMonth >= Month(@datFrom) and @iMonth <= month(@datTo)
    set @returnvalue 0
    when month(@datFrom) = month(@datTo)
    @set @returnvalue = @cBudget
    when month(@datFrom) = @iMonth
    set @returnvalue = (@iDaysInMonth + 1 - day(@datFrom)) / @iDuration * @cBudget
    when month(@datTo) = @iMonth
    set @returnvalue = Day(datToDate) / iDuration * cBudget
    when @iMonth > month(@datFrom) and @iMonth < month(@datTo)
    set @returnvalue = @iDaysInMonth / @iDuration * @cBudget
    end

    return @returnvalue
    END


    This is my first post to this forum - so any constructive criticism will be welcomed.

    Basically, where am I going wrong? - have I got the wrong end of the stick? Have I got the wrong stick? Have I got a stick of dynamite?!

    All help greatfully received,
    Paul
    Last edited by PaulDavies; 01-09-07 at 15:39.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Case statements work a little differently in T-SQL. Try this:
    Code:
    CREATE FUNCTION [dbo].[calcPercentOfBudget] (@datFrom as datetime, @datTo as datetime , @iMonth as int, @cBudget as money, @GetDate as datetime)
    RETURNS money AS
    BEGIN
    declare @iDuration int
    declare @returnvalue money
    declare @sRatio decimal
    declare @iDaysInMonth int
    
    set @iDuration = datediff(d, @datFrom, @datTo)
    set @iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@iMonth as varchar) + '/' + cast(year(@getdate) as varchar))
    
    select @returnvalue = 
       case when year(@datFrom) <> year(@getdate) or year(@datTo) <> year(@getdate) then 0
    	when not(@iMonth >= Month(@datFrom) and @iMonth <= month(@datTo) then 0
    	when month(@datFrom) = month(@datTo) then @cBudget
    	when month(@datFrom) = @iMonth then (@iDaysInMonth + 1 - day(@datFrom)) / @iDuration * @cBudget
    	when month(@datTo) = @iMonth then Day(datToDate) / iDuration * cBudget
    	when @iMonth > month(@datFrom) and @iMonth < month(@datTo) then @iDaysInMonth / @iDuration * @cBudget
    end
    
    return @returnvalue
    END
    Case in T-SQL returns a value, rather than executes a block of code.

  3. #3
    Join Date
    Jan 2007
    Posts
    2

    result!

    fantastic, after a couple of other debugs from translation I have a result that works!

    Many thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •