Next Business day (Complex)

[nmunro]

Hi there,

I've been banging my head against a brick wall on this problem all day and need some assistance. My SQL's still not that great but I still feel that this is quite a difficult problem.

I'm trying to return a view, with some processing going on "on-the-fly" to populate a single column. I've written the view as a proc because of this calculation dependancy and use of variables.

What I need to be is calculate the next business day for every single row in the returned table (on the fly as the view is created), with there being a dependancy on when the next business day is contained in each row itself.

I'm also limited in that the server this proc is running on is SQL Server 7 so no functions! Arghhh!!!

Here's a bit of code that illustrates what i'd like to do (I know this doesn't work because you can't have a while in a select) but it should give you an idea.



DECLARE @date datetime
SET @date = CURRENT_TIMESTAMP + 1 -- Tomorrow's Date

SELECT AccrualAmount=(
CASE WHEN d.market<>'DEP' THEN 0
ELSE CASE
WHEN CURRENT_TIMESTAMP + 1 > d.maturity THEN 0
ELSE
-- Find the next Business date
WHILE (DATEPART(d,@date) IN (6,7) OR @date IN (SELECT holiday from holidays WHERE ccyname = a.paidinccy))
BEGIN
-- Increment the date
SET @date = @date + 1
END

SELECT (d.premium * (
(DATEDIFF(d, d.value_date, @date)) -- Days Passed
/
(DATEDIFF(d, d.value_date, d.maturity))
)) -- Total LifeTime of Depo
END
END
)


Is there any way that I can do this sort of calculation ion the fly in this way without messing around with temporary tables etc.?

Any help would be much appreciated!