Need Help w/UDF

Last Post 13 Nov 2006 06:38 PM by mwesch. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
13 Nov 2006 12:46 PM
Below is a UDF that is used in my app. After the, END I have commented in a section of vbNet code that contains some logic I would like to add to this function. Immediately in the comment I indicate some equivalencies from the .net to what is already another udf or contained in this udf.

You can ignore completely the "Format" as 0.00% move as well as the division by 100.

Could you translate the .net into SQL and append this function as a udf called AnnualizedPctReturnNEW and put it in my app?

That would be a huge help to me.

---------------------------------
CREATE FUNCTION dbo.AnnualizedPctReturn ( @HoldingID as Integer, @ReturnMonth as SmallDateTime)

RETURNS Numeric (16, 4) AS

BEGIN
DECLARE @FDY SmallDateTime
DECLARE @DaysSoFar Integer
DECLARE @Yr Varchar(4)
DECLARE @YtdRtnPct Numeric (16, 4)
SET @Yr = DatePart(YEAR, @ReturnMonth)
SET @YtdRtnPct = (Select max(dbo.ReturnSummary.ReturnPCT) from dbo.ReturnSummary where (HoldingID = @HoldingID) AND (ReturnSummaryMonth = 'YTD' + @Yr ))


SET @FDY = Convert(SmallDateTime, (SELECT DATEADD(yy, DATEDIFF(yy, 0, @ReturnMonth), 0)))
-- get the number of days so far in the year
SET @DaysSoFar = DateDiff(DAY, @FDY, @ReturnMonth)

Return (365 / Convert(Numeric (16, 4), @DaysSoFar)) * ( @YtdRtnPct )
END


/*


'THE BELOW IS CODE FROM the xlReport class.....AT SOME POINT IT SHOULD BE INCORPORATED INTO THIS UDF.
.InitialInvestmentDate = GetInitialInvestmentDate(@HoldingID)
rptEndDate = @ReturnMonth
.YTDReturnPct = @YtdRtnPct

If CType(.InitialInvestmentDate, Date) > FirstDayOfYear Then
r("AnnualizedReturnPct") = Format(((365 - CType(.InitialInvestmentDate, Date).DayOfYear) / (rptEndDate.DayOfYear - CType(.InitialInvestmentDate, Date).DayOfYear)) * (CType(.YTDReturnPct, Double) / 100), "0.00%")
Else
r("AnnualizedReturnPct") = Format((CType(.AnnualizedReturnPct, Double) / 100), "0.00%")
End If

*/
mwesch
New Member
New Member

--
13 Nov 2006 06:38 PM
I think the following might give you a start. I didn't put it into a function, but the logic is there for you to use. The only question I had is that it appears that .AnnualizedReturnPct must also be an input parameter as an attribute of an investment. If the initial investment date is from a prior year, then the function returns this value instead of a prorated percented based on YTD gains.

--declare and initialize input parameters
----------------------------------------
declare @InitialInvestmentDate datetime,
@AnnualizedReturnPct numeric(5,2),
@YtdRtnPct numeric(5,2),
@ReportEndDate datetime

select @InitialInvestmentDate = '2/11/2006',
@AnnualizedReturnPct = 7.0,
@YtdRtnPct = 12.0,
@ReportEndDate = getdate()
----------------------------------------

--declare and initialize variables
----------------------------------------
declare @FirstDayOfYear datetime,
@FirstDayOfYearDay numeric(5,0),
@InitialInvestmentDateDay numeric(5,0),
@ReportEndDateDay numeric(5,0)

select @FirstDayOfYear = convert(datetime, convert(varchar, year(getdate())) + '-01-01'),
@FirstDayOfYearDay = 1,
@InitialInvestmentDateDay = datepart(dy, @InitialInvestmentDate),
@ReportEndDateDay = datepart(dy, @ReportEndDate)

--declare output parameter
----------------------------------------
declare @AnnualizedReturnPct_OUTPUT numeric(5,2)
----------------------------------------

--calculate annualized percentage
----------------------------------------
if @InitialInvestmentDate > @FirstDayOfYear
begin
select @AnnualizedReturnPct_OUTPUT = (365.00 - @InitialInvestmentDateDay) / (@ReportEndDateDay - @InitialInvestmentDateDay) * @YtdRtnPct
end
else
begin
select @AnnualizedReturnPct_OUTPUT = @AnnualizedReturnPct
end
----------------------------------------

--return results
----------------------------------------
select @AnnualizedReturnPct_OUTPUT as AnnualizedReturnPct

--select @InitialInvestmentDate as InitialInvestmentDate,
--@InitialInvestmentDateDay as InitialInvestmentDateDay,
--@FirstDayOfYear as FirstDayOfYear,
--@FirstDayOfYearDay as FirstDayOfYearDay,
--@ReportEndDate as ReportEndDate,
--@ReportEndDateDay as ReportEndDateDay,
--@AnnualizedReturnPct_OUTPUT
----------------------------------------


You are not authorized to post a reply.

Acceptable Use Policy