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:eek: