Can you use movenext in a function

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

Can you use movenext in a function

Author

Topic

cgsanders7
Starting Member

5 Posts

Posted - 2007-05-15 : 12:54:28

I am creating a user defined function and I need to do calculations on each record in a table and the only way I can see to do this is to use movenext or something like that. If you know anything about this please let me know. Thanks.

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-05-15 : 13:10:59

What do you want to do after calculating? It may not be necessary to loop through the records in the table.

If you post the complete details, some one will help you.

cgsanders7
Starting Member

5 Posts

Posted - 2007-05-15 : 13:53:05

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.

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-05-15 : 14:19:16

Look into Books Online for Table valued functions. Here is an example -

CREATE FUNCTION Sales.fn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
);
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources