Eric Mamet (11/7/2013)
Hi everyone, sorry for being so slow but I just did not have time to go through the article before...Yes, it is very interesting and intriguing but it looks like I won't be able to use that due to the complexity of my UDF.
CREATE FUNCTION dbo.udf_ResidualValue(@RVDate DATETIME, @GARMENTSTATUS INT
, @RVMATRIXCODE VARCHAR(10)
, @LIFETIME INT
, @GARMENTISSUEDATE DATETIME
, @STARTPRICE NUMERIC(28,12)
, @ENDPRICE NUMERIC(28,12)
, @TERMINATIONDISCPCT NUMERIC(28,12)
, @STS_CONTINUOUSCHARGING INT
, @WEARERSTATUS INT
, @SETQTY INT
, @GarmentNumber INT
)
RETURNS DECIMAL(9,2)
AS
BEGIN
IF ( @RVDate < @GARMENTISSUEDATE )
RETURN 0.0;
IF @SETQTY > 0
AND @GarmentNumber > @SETQTY
RETURN 0.0;
IF LEN(LTRIM(@RVMATRIXCODE)) = 0
RETURN 0.0;
IF (@TERMINATIONDISCPCT = 100)
RETURN 0.0;
DECLARE @GarmentAgeInMonth INT
DECLARE @DateX DATE
DECLARE @DateY DATE
DECLARE @Sign INT
IF(@GARMENTISSUEDATE < @RVDate)
BEGIN
SET @DateX = @GARMENTISSUEDATE
SET @DateY = @RVDate
SET @Sign = 1
END
ELSE
BEGIN
SET @DateX = @RVDate
SET @DateY = @GARMENTISSUEDATE
SET @Sign = -1
END
SELECT @DateY = DATEADD(day, 1, @DateY);
SET @GarmentAgeInMonth = @Sign * (
SELECT
CASE
WHEN DATEPART(DAY, @DateX) = DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY)
WHEN DATEPART(DAY, @DateX) = DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY)
WHEN DATEPART(DAY, @DateX) >= DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
ELSE DATEDIFF(MONTH, @DateX, @DateY)
END
)
DECLARE @RVRatio FLOAT
IF @TERMINATIONDISCPCT IS NULL
SET @RvRatio = 1.0;
ELSE
SET @RvRatio = ( 100.00 - @TERMINATIONDISCPCT ) / 100.00;
IF @LIFETIME > 0 AND @GarmentAgeInMonth <= @LIFETIME
BEGIN
-- Calculate the monthly depreciation to 3 digits to imitate the exact Axapta calculation
DECLARE @MonthlyDepreciation DECIMAL(18, 3)
SELECT @MonthlyDepreciation = (@StartPrice - @EndPrice) / @LIFETIME;
RETURN CAST( @StartPrice - (@MonthlyDepreciation * @GarmentAgeInMonth ) AS DECIMAL(28, 2)) * @RVRatio;
END
IF @STS_CONTINUOUSCHARGING = 1
AND @LIFETIME > 0
AND @GarmentAgeInMonth > @LIFETIME
RETURN @ENDPRICE * @RVRatio;
RETURN 0.0;
END
In fact, there are even business type arguments around this function so I wrote another one returning the reason for the value rather than the value itself so that I can explain my results as well.
Having said that, I have learnt some very interesting stuff about UDFs.
Thanks
Eric
I am wont have time today but will take a stab at this tonight. In the meantime, to demonstrate how you would convert yours into an iTVF, I put this example together. It demonstrates the techniques that you would use. Note my comments:
-- Scalar Valued Function
CREATE FUNCTION dbo.SVF(@x int, @y int, @d date)
RETURNS int AS
BEGIN
DECLARE @return_value int;
IF @d>getdate()
RETURN 0;
IF @x=0
RETURN 0;
SET @x=@x+(2*@y)
SET @y=@y*2;
IF @x>@y
BEGIN
SET @return_value=@x*3
END
ELSE
BEGIN
SET @return_value=@y
END
RETURN @return_value
END
GO
-- the new inline table valued function version (aka iSVF)
CREATE FUNCTION dbo.iSVF(@x int, @y int, @d date)
RETURNS TABLE
AS
RETURN
WITH aggreagations AS
(SELECTx = @x+(2*@y),
y = @y*2,
d = @d)
SELECT return_value=
CASE WHEN x>y THEN x*3 ELSE @y END *
CASE WHEN @d>getdate() THEN 0 ELSE 1 END *
CASE WHEN @x=0 THEN 0 ELSE 1 END
FROM aggreagations;
GO
--test the functions and look at the query plans
SELECT * FROM dbo.iSVF(1,2,'1/1/2001')
SELECT dbo.SVF(1,2,'1/1/2001')
--using iSVF
CREATE TABLE #sample
(s_id int primary key,x int not null, y int not null, d date not null);
--sample data
INSERT INTO #sample
SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY (SELECT null)),
1.0+floor(10*RAND(convert(varbinary, newid())))-1,
1.0+floor(14*RAND(convert(varbinary, newid()))),
getdate()-4000+(1.0+floor(5000*RAND(convert(varbinary, newid()))))
FROM master..spt_values;
-- old way
SELECT s_id, x,y,d,dbo.SVF(s.x,s.y,s.d) AS val
FROM #sample s
--new way
SELECT s_id, x,y,d,return_value
FROM #sample s
CROSS APPLY dbo.iSVF(s.x,s.y,s.d)
Edit: added s_id to select statements.
-- Itzik Ben-Gan 2001