UDF Help - calculation... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UDF Help - calculation...

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2009-02-27 : 12:34:48

Hi guys,
Im trying to create a UDF to calculate Effective cost of a mobile phone contract...
Here is my attempt.

Basically - heres how it should work.

Need to pass in TariffID, to select ContractType, in order to get contract length 1, 12, 18, 24 months etc...
The rest is passed in as parameters (it is going to be a computed column on a table which has all these params...)

The calculation is-

work out the total cost of the contract - this is the line rental multiplyed by number of months

work out total discount:

Work out the value (in money) of months free
Work out the value (in money) of months half price
add those 2 values together, and add cost offset (cost offset is a negative number)

that is then the total discount

then, subtract the total discount, from the total cost, that leaves you with the actual cost

return the actual cost, divided by the number of months.

Here is my attempt... it returns 0 or null at the moment...
Any ideas?

CREATE function dbo.fn_GetEffectiveCost (
@TariffID varchar(50),
@MonthsFree smallint,
@MonthsHalf smallint,
@Cashback money
)

RETURNS int

AS BEGIN

Declare @finalResult MONEY

DECLARE @LineRental money
DECLARE @CostOffset money
DECLARE @ContractLength int
DECLARE @ContractType varchar(3)

SELECT @ContractType = ContractType FROM Tariff WHERE TariffID = @TariffID
SELECT @CostOffset = CostOffset FROM Tariff WHERE TariffID = @TariffID

SELECT @ContractLength = REPLACE(ContractType, 'm', '')
FROM Tariff WHERE TariffID = @TariffID;

DECLARE @TotalCost money
DECLARE @TotalDiscount money

SET @TotalDiscount = ((@LineRental * @MonthsFree)
+ ((@LineRental / 2)* @MonthsHalf)
+ @Cashback)
- @CostOffset

SET @TotalCost = (@LineRental * @ContractLength)

RETURN (@TotalCost-@TotalDiscount)/@ContractLength

END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-27 : 12:39:17

One reason you may be getting NULLs is that if any of the parameters is NULL (ie: no MonthsHalf value) then the entire result will be null. You need to COALESCE these values with the appropriate default value. Also I assume @contractLength has to be greater than 0 but if it can be zero then you will get a devide by zero error.

EDIT:
looks like @lineRental is not being set anywhere so that will always cause a NULL result.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources