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:Case statements work a little differently in T-SQL. Try this:

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.|||fantastic, after a couple of other debugs from translation I have a result that works!

Many thanks :)

New options required in RS

Hi. Hope this reaches the RS development team.
I would like to know if in the new releases of RS there will be
expressions as there are in the DTS visual studio.
For example in the fields of width, height..at the moment we have to
put a fixed width and height whereas we could use the expressions.
Sometimes customers request double spacing instead of single space btw
records.
In addition to that, i need extra options in the matrix such as:
the ability to add fields to the right of the matrix column, (static
fields) that are not related to the matrix's expansion. The result
would be the possibility of having static columns at the left and right
of the matrix...what an improvement it would be.
Also, right now we are unable to add headers to the fields preceeding
the matrix column. This would be a of great help in explaining the
content of these fields. Right now i'm creating labels and placing them
on top of the matrix but it's not legitimate in a way when i export to
excel they are misplaced.
Thanks
Christinato add to this Allow the Duplicate Data in the Group Field so it can be
exported to excel and sorted :)
"Christina" wrote:
> Hi. Hope this reaches the RS development team.
> I would like to know if in the new releases of RS there will be
> expressions as there are in the DTS visual studio.
> For example in the fields of width, height..at the moment we have to
> put a fixed width and height whereas we could use the expressions.
> Sometimes customers request double spacing instead of single space btw
> records.
> In addition to that, i need extra options in the matrix such as:
> the ability to add fields to the right of the matrix column, (static
> fields) that are not related to the matrix's expansion. The result
> would be the possibility of having static columns at the left and right
> of the matrix...what an improvement it would be.
> Also, right now we are unable to add headers to the fields preceeding
> the matrix column. This would be a of great help in explaining the
> content of these fields. Right now i'm creating labels and placing them
> on top of the matrix but it's not legitimate in a way when i export to
> excel they are misplaced.
> Thanks
> Christina
>

New Datawarehouse

What is the best way to set up a datawarehouse? Do I use replication or dts
packages or what? What I need to do is archive production data every two
weeks to this datawarehouse. Then I will need to be able to query the
archive data. What is the best way to do this? And how do I go about doing
it?
Thanks in advance for the help.
Dave> What is the best way to set up a datawarehouse? Do I use replication or
dts
> packages or what? What I need to do is archive production data every two
> weeks to this datawarehouse.
DTS seems more appropriate in this scenario.
> Then I will need to be able to query the
> archive data. What is the best way to do this? And how do I go about doing
> it?
DW is usually 1st step - next step are OLAP cubes. Do please read more on
OLAP in Books OnLine, I guess this is what you need.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com