Function with Expression to return values

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

General SQL Server Forums

New to SQL Server Programming

Function with Expression to return values

Author

Topic

cgsanders7
Starting Member

5 Posts

Posted - 2007-05-15 : 12:03:33

I have created a function to return values, which works fine, but I can't do calculations in it.

CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT DISTINCT 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) )

I need to do calculations on market value based on the default date.
If default date isn't specified then it should be 100% of par amount.
If default date is less than one year ago - 65% of the par_amount.
If default date is one or more years ago - 0.
I have no idea about how to do this and everything I try wont work.
I created another function to do the calculations and this seems to work, but it only does one record instead of all of them.

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

I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-15 : 12:07:29

Why are you using DISTINCT everywhere? If you have values of 2 + 2 + 1, SUM(Distinct X) will return 3! Shouldn't that be 5 ?


select sum(distinct x) as DistinctSum, sum(x) as RealSum
from
 (select 2 as x union all 
  select 2 union all 
  select 1 ) y

DistinctSum RealSum
----------- -----------
3           5

(1 row(s) affected)

- Jeff
http://weblogs.sqlteam.com/JeffS

cgsanders7
Starting Member

5 Posts

Posted - 2007-05-15 : 12:19:23

For some reason it doesn't the values don't come out right unless I use distinct. I don't know why, but maybe there is something wrong in the code, but when I include distinct it works.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-15 : 12:33:05

All I can suggest is to test it out yourself on your data (use a small, filtered subset) to see for sure; you may be lucky and all of the dollar amounts you are adding may be unique, but I can assure you that you do NOT want SUM(Distinct ) .... Also, be sure that you completely understand what sum(distinct) means and does; it is rarely used (if ever) because it just isn't very useful in most cases.

- Jeff
http://weblogs.sqlteam.com/JeffS

cgsanders7
Starting Member

5 Posts

Posted - 2007-05-15 : 12:56:34

For some reason when I don't use sum distinct it comes back with a value way greater than it should be, and then I change it back to sum distinct and it works. I really don't know why, but it's the only way it works. Thanks for your insight though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources