User Defined Function with Calculations

  • Thread starter Thread starter cgsanders7
  • Start date Start date
C

cgsanders7

I am trying to write a function that returns a table representing all assets
for a given deal aggregated on the issue level of any date. The function
tf_Asset_Portfolio(@deal_id, @as_of_date) should return the following fields:
issue_id
par_amount - total_par_amount for an issue
market_value - total calculated market value for an issue; market value
calculated as follows: for non-defaulted issues (default_date is not
specified) - 100% of par_amount, for issues that defaulted less than one year
ago - 65% of par_amount, for issues that are one or more years in default - 0.


The table layout is as follows:
Assets-
deal_id
issue_id
par_amount

Issue-
issue_id
issuer_id
default_date

Issuer_Rating_History
issuer_id
as_of_date

Market value isn't a field in any table but the return table and that is the
field I'm trying to do the calculation on. Anyone have an idea about how to
do this because everything I try isn't working. I can return a table without
calculating the field or I can return a calculation on one field but I dont
know how to tie both of these together. Here are my examples that cant do
both. Thanks in advance.

This one can return the table without the calculation -
CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS
par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value
FROM dbo.Issue INNER JOIN
dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id
INNER JOIN
dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.
Issuer_Rating_History.issuer_id
WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date)
GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date
HAVING (dbo.Assets.deal_id = @deal_id) )

This one can do the calculation -
CREATE FUNCTION dbo.tf_Asset_Portfolio2
(@deal_id int,
@as_of_date datetime)
RETURNS @Market TABLE
(issue_id int, par_amount money, market_value money)
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @DD datetime
DECLARE @PA money
DECLARE @MV money
DECLARE @ID int
DECLARE @DateD int

SELECT TOP 1
@ReturnDate = LAST_BATCH
FROM master..sysprocesses
WHERE SPId = @@SPID

SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id
SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID
AND Assets.deal_id = @deal_id
SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID

SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)

If @DD = Null
BEGIN
SET @MV = @PA
END
Else If @DD > @ReturnDate
BEGIN
SET @MV = @PA
END
Else If @DateD < 1
BEGIN
SET @MV = @PA * .65
END
Else If @DateD >= 1
BEGIN
SET @MV = 0
END

insert into @Market
(issue_id, par_amount, market_value)
values
(@ID,@PA,@MV)

RETURN
END

Please help, I think I'm going in the wrong direction.
 
First, if your question is strictly about T-SQL then you should ask this
question in a more appropriate newsgroup like m.p.sqlserver.programming.
This one is about problem related to both ADP and SQL-Server.

Second, it's very hard to read code where variables make an heavy use of the
underscore character _ . You should use the Camel representation instead.

Third and more important, your use of the charactere - as an alias for : and
not using separate lines for the description of each variable and parameter
make reading your description nearly impossible.

Fourth, you are using the same call to SUM(DISTINCT dbo.Assets.par_amount)
for both par_amount and market_value.

Fifth, something like (dbo.Assets.deal_id = @deal_id) shouldn't be in a
HAVING statement but in a WHERE statement.

Sixth, instead of using the little trick about master..sysprocesses; simply
sending the value of GetDate() as a parameter would be a much more simpler
idea.

And finally, you have a problem probably because you want too much to use
functions returning a table. Your first choice should be to have a stored
procedure, using select statements, subqueries and temporary tables as
necessary. Using a lot of unecessary table returning UDFs will only make
your code un-readable.

I don't understand your code but probably that you should make a joint
statement on the two tables returned by these functions using issue_id as
the relationship. See:

http://www.databasejournal.com/features/mssql/article.php/1438081

but probably that replacing these two functions would be a much better idea.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


cgsanders7 said:
I am trying to write a function that returns a table representing all
assets
for a given deal aggregated on the issue level of any date. The function
tf_Asset_Portfolio(@deal_id, @as_of_date) should return the following
fields:
issue_id
par_amount - total_par_amount for an issue
market_value - total calculated market value for an issue; market value
calculated as follows: for non-defaulted issues (default_date is not
specified) - 100% of par_amount, for issues that defaulted less than one
year
ago - 65% of par_amount, for issues that are one or more years in
default - 0.


The table layout is as follows:
Assets-
deal_id
issue_id
par_amount

Issue-
issue_id
issuer_id
default_date

Issuer_Rating_History
issuer_id
as_of_date

Market value isn't a field in any table but the return table and that is
the
field I'm trying to do the calculation on. Anyone have an idea about how
to
do this because everything I try isn't working. I can return a table
without
calculating the field or I can return a calculation on one field but I
dont
know how to tie both of these together. Here are my examples that cant do
both. Thanks in advance.

This one can return the table without the calculation -
CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount)
AS
par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value
FROM dbo.Issue INNER JOIN
dbo.Assets ON dbo.Issue.issue_id =
dbo.Assets.issue_id
INNER JOIN
dbo.Issuer_Rating_History ON dbo.Issue.issuer_id =
dbo.
Issuer_Rating_History.issuer_id
WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date)
GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id,
dbo.Issue.default_date
HAVING (dbo.Assets.deal_id = @deal_id) )

This one can do the calculation -
CREATE FUNCTION dbo.tf_Asset_Portfolio2
(@deal_id int,
@as_of_date datetime)
RETURNS @Market TABLE
(issue_id int, par_amount money, market_value money)
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @DD datetime
DECLARE @PA money
DECLARE @MV money
DECLARE @ID int
DECLARE @DateD int

SELECT TOP 1
@ReturnDate = LAST_BATCH
FROM master..sysprocesses
WHERE SPId = @@SPID

SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id
SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id =
@ID
AND Assets.deal_id = @deal_id
SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID

SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)

If @DD = Null
BEGIN
SET @MV = @PA
END
Else If @DD > @ReturnDate
BEGIN
SET @MV = @PA
END
Else If @DateD < 1
BEGIN
SET @MV = @PA * .65
END
Else If @DateD >= 1
BEGIN
SET @MV = 0
END

insert into @Market
(issue_id, par_amount, market_value)
values
(@ID,@PA,@MV)

RETURN
END

Please help, I think I'm going in the wrong direction.
Click to expand...
 
I am writing it in an ADP, so I figured this is the right spot. I didn't
create the variables or the tables, but I do have to use them just the way
they are. I know I'm using the same call for both par_amount and market_value.
I just left that in there so someone would be able to understand where I'm
coming from and that market value = a calculation on par value. I don't know
how you can get GETDATE() to work in a function, because I've tried
everything and it wont let me use GETDATE() in a function. I was told to make
a table, it's not because I wan't to. I looked in the articles you sent me
and I don't see anything of use in it. My goal is to replace this code with
one function, not to create a join on the table. I want one table function
with the calculated values in it.
 
First, sorry for my comment about the Having statement; I've just noticed
the Group By All instruction instead of the more commun Group By but all
these _ are mind boggling.

As for the newsgroup, you should always go where you can find the greatest
number of knowledgeable people about your specific question. Even if you
are calling your function from ADP, obviously this question relates only to
T-SQL.

As to your answer, it's hard to give you an answer because you don't explain
at all the relationship between the table Assets, the table Issue and the
variable deal_id. For example, Is deal_id the primary key for the table
Assets or it is the primary key for an yet another table? Can the table
Assets have many rows with the same issue_id? Can the table Assets have
many rows with the same deal_id? Can the table Assets have many rows with
the same issue_id and the same deal_id?

In the first function, you are grouping on both issue_id and default_date;
does this mean that you can have the same issue_id with different
default_date?

Without telling us how many rows with the same issue_id can be returned by
both the first and the second function; how are you expecting people to give
you the correct answer on how to fuse together these two functions?

As a wild guess, I suppose that you should replace the second function with
a scalar function that will take the @deal_id and the @issue_id as parameter
and use this new function to compute the value of market_value in the first
function; however, this is probably wrong because at this moment, you are
passing the value of @as_of_date to the second function but you never use it
inside it; so the real relationship because @as_of_date and the result
returned by the second function is unknown.

But in all case, as you seem to search for the value of maket_value in the
first function, I suppose that you should probably replace the second
function with either a scalar function (with the proper parameters, of
course) or a simpler sub-query.
 
Back
Top