getting rid of a loop...

Question

getting rid of a loop...

steven powell

Mr or Mrs. 500

Points: 557
More actions
November 12, 2004 at 7:46 am

#161933

Hi, guys.
just wondering if there's a way of getting rid of the loop in the section of code below... i was thinking about having a table with positive integers in it but that didn't feel particualy good...
any ideas would be great
thanks in advance...
s
ps. i'm looking at working with values of @pMonths of about 200 (not massive i know)
create proc p_bond_benchmark_xref_EUR (@pMonths int = 199)
as
begin
declare @now datetime, @month datetime
create table #mDates(maturity_dt datetime)
select @err = @@error, @rc = @@rowcount, @msg = '10 - Error creating #mDates'
if @err != 0 goto Exception

select @now = getdate(), @month = substring(convert(varchar, getdate(), 112), 1, 6) + '01'
while @pMonths > 0 --this is the loop i'd like to reduce to a set based solution...
begin
    insert #mDates(maturity_dt) select dateadd(mm, +@pMonths, @month)
    select @err = @@error, @msg = '20 - Error populating #mDates'
    if @err != 0 goto Exception
    select @pMonths = @pMonths - 1
end

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

While a number might not be helpful in your case, have you considered creating a permanent date table?

Btw, let me say, I work as an asset manager, and SQL Server isn't the tool of choice for such analysis you evidently are about to code. Many of these performance analysis and benchmarking thingies do require some kind of looping. So either you use a cursor or better do this in some kind of procedural programming language. That's an easy exercise for them. Personally, I only load the data from SQL Server to Excel and do the rest there.

Here's an interesting article by Joe Celko on doing financial analysis with SQL

http://www.intelligententerprise.com/online_only/celko/030303_1.jhtml

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

steven powell Mr or Mrs. 500 Points: 557 More actions · Answer 2

I hadn't thought of a permanent date table (even though i swear by them for OLAP dimensions)... what i'm doing in the rest of the proc is picking off an instrument to use as a benchmark for a bond trade, so now there's no need for looping of any sort after i add a Date table to the DB!

cheers, frank!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Aah, doing this fixed income relative value thing. It's getting interesting when you need to implement swap spread models, volatility forecasting or skew models.

Good luck

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]