Translat Function from Access to SQL 2

[MoobyCow]

Hi all,

I'm very new to SQL server and I'm trying to get started by translating some fairly simple functions in Access to SQL Server 2000 for performance reasons.

Here's a good example. I'm calculating the "Return on Investment" for cashflows from a DB in Access I embed the function call (GetROI) in the Query like so:

SELECT FundPerformance.Fund_ID, FundPerformance.Date, getROI([date],[fund_id]) AS ROI, FROM FundPerformance
WHERE (((FundPerformance.Subscriptions)>0));

The Function is as follows:

Function getROI(ByVal aDate As Date, ByVal FundID As String) As Double
On Error Resume Next
Dim sql As String
Dim rs As Recordset
Dim ROI As Double

SELECT FundPerformance.Fund_ID, FundPerformance.Date, getROI([date],[fund_id]) AS ROI, FROM FundPerformance
WHERE (((FundPerformance.Subscriptions)>0));

Function getROI(ByVal aDate As Date, ByVal FundID As String, PGAFUND As String) As Double
On Error Resume Next
Dim sql As String
Dim rs As Recordset
Dim ROI As Double

sql = "Select [MTD ROR], Fund_ID, Date From fundPerformance where (Fund_id like '" & Replace(FundID, "'", "''") & "') AND (Date >= #" & Replace(aDate, "'", "''") & "#) ORDER BY FundPerformance.Date;"
Set rs = CurrentDb.OpenRecordset(sql)

rs.MoveFirst
ROI = rs.Fields(0)
rs.MoveNext
While Not rs.EOF
ROI = ((1 + ROI) * (1 + rs.Fields(0)) - 1)
rs.MoveNext
Wend
getROI = ROI
Set rs = Nothing
End Function

This returns a double (ROI) in each row of the query. What would be the best way to go about doing this on a SQL Server?

I think one good example of this sort of thing and I'll be fine, I'm just having trouble finding any examples of something similar.

 

[bboffin]

Your example code is confusing as getROI seems to be defined twice.

However read up on 'User Defined Functions' and 'Cursors' in SQL Books Online. You should be able to reproduce the getROI function using a table cursor.



Bob Boffin

 

[MoobyCow]

Opps, a bad copy paste job:

Query:

SELECT FundPerformance.Fund_ID, FundPerformance.Date, getROI([date],[fund_id]) AS ROI, FROM FundPerformance
WHERE (((FundPerformance.Subscriptions)>0));

The Function:

Function getROI(ByVal aDate As Date, ByVal FundID As String) As Double
On Error Resume Next
Dim sql As String
Dim rs As Recordset
Dim ROI As Double

sql = "Select [MTD ROR], Fund_ID, Date From fundPerformance where (Fund_id like '" & Replace(FundID, "'", "''") & "') AND (Date >= #" & Replace(aDate, "'", "''") & "#) ORDER BY FundPerformance.Date;"
Set rs = CurrentDb.OpenRecordset(sql)

rs.MoveFirst
ROI = rs.Fields(0)
rs.MoveNext
While Not rs.EOF
ROI = ((1 + ROI) * (1 + rs.Fields(0)) - 1)
rs.MoveNext
Wend
getROI = ROI
Set rs = Nothing
End Function

 

[bboffin]

Try something like this:

CREATE FUNCTION getROI (@Date as datetime, @FundID as nvarchar(50))

RETURNS float

AS
begin
  DECLARE @ROI float
  SET @ROI = 0
  DECLARE @MTD_ROR float
  DECLARE ROI_Cursor CURSOR FOR
     Select [MTD ROR] From fundPerformance where (Fund_id like Replace(Replace(@FundID, '''', ''''''), '*', '%'))  AND (Date >= @Date) ORDER BY FundPerformance.Date
  OPEN ROI_Cursor
  FETCH NEXT FROM ROI_Cursor INTO @MTD_ROR
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @ROI = ((1 + @ROI) * (1 + @MTD_ROR) - 1)
    FETCH NEXT FROM ROI_Cursor INTO @MTD_ROR
  END
  CLOSE ROI_Cursor
  DEALLOCATE ROI_Cursor
  RETURN @ROI
end

The Select statement might need a bit of tweaking as I'm guessing what you're allowed to enter in it but it should give you an idea.

Notice that it's not that different to using a recordset.


Bob Boffin

 

[hwkranger]

bboffin has a solution that looks like it works. I will warn you that Cursors are the devil.

I'll look at this some more and see if I can offer you a cursor-less solutions. (Sometimes they are needed, but most times you can do without..)



Randall Vollen
National City Bank Corp.

 

[hwkranger]

As I look at this more -- I think you're stuck with the cursor.

Good job Bboffin on the solution.

Randall Vollen
National City Bank Corp.

 

[MoobyCow]

That works perfectly, thanks.

I'm glad I asked about this because even if I figured out the function, that replace in the select statement would have taken me a while.

 

[ESquared]

This doesn't work?

CREATE FUNCTION getROI (@Date datetime, @FundID varchar(50))
RETURNS FLOAT
AS
BEGIN
   DECLARE @ROI float
   SET @ROI = 1

   SELECT @ROI = @ROI * ([MTD ROR] + 1)
   FROM FundPerformance
   WHERE
      Fund_id = FundID
      AND [Date] >= @Date

   RETURN @ROI - 1
END

Multiplication is commutative so you don't need an order by. And if the order is irrelevant, why the heck use a cursor? (You might get away without one in any case.) And you can get away with a little less math, just move the +1 and -1 out of the query.

I'm not sure that you actually need to replace single quotes with double quotes. You should test it.

Erik

 

[MoobyCow]

ESquared,

That does work, thank you. The ordering is in there because in similar functions I select the date at which an investment goes negative and then the date at which it recovers from going negative etc.

I just gave a simple example of a function so that it would be easier to get an answer.

 

[ESquared]

For what it's worth, you can probably get those values about the date an investment goes negative or positive also without using a cursor. But you'd have to decide how the values got returned.