Previous Row Calculations (sql server 2000)

if anyone know a way that you can look up a value from the previous row in a View to do a calculation on (in sql server 2000)
for example:
expr1=PreviousRow.Expr1/30 + expr2 - expr3 =100

so the next row is

expr1=100/30 + expr2 - expr3 =300

so the third row is

expr1=300/30 + expr2 - expr3 =100
or tell me if it not possible please

and special thanks to Umachandar Jayachandran - MS for help.

Hi,

Im not quite sure if this is possible in a view. But you can do this in a stored proc. You can use a cursor and manually traverse your records...

cheers,

Paul June A. Domag

|||Thank you my frind for your help but I need give me some examples of the crusors to understand that...

if you can.....
|||

Hi,

Here's a sample in transact sql:

-- Declare the variables to store the values returned by FETCH.
DECLARE @.au_lname varchar(40), @.au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- here is where you would do your calculations

-- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @.au_fname + ' ' + @.au_lname

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

BTW, this would be placed inside a stored proc...

cheers,

Paul June A. Domag

|||

Bear with me here, as the info is worth it!!!

do you really need a table for this?

Ahhh, memories of my analysis classes!!!

This is what is called a Discrete Dynamic Equation (also called a Difference equation as oppposed to a differential equation) ; specifically, a Discrete first-order Affine Dynamical System

The form is:

A_n = R A_n-1 + B

The general solution for which is:
A_k=CR^k + B/(1-R)

The particular solution for n=0 is

A₀=CR⁰+ B/(1-R)=C+B/(1-R)

Solve for C

C=A₀-B(1-R)

Therefore, the particular solution for the dynamical system is:

A_k=R^k(A₀-B/(1-R))+B/(1-R)
in sql:

Create function AffineDynamic(@.A0 real, @.R decimal(16,16), @.B real, @.k int) returns decimal(32,16)
as
begin
declare @.result decimal(32,16)
if @.k = 0
set @.result = @.A0
else
set @.result Power(@.R,@.k) * (@.A0 - (@.B * Power(1-@.R, -1))) + (@.B * Power(1-@.R, -1))
return @.result
end
usage:


-- a random expr1 for row 1
DECLARE @.initial real
set @.initial = 25

-- the rate: 1/30
DECLARE @.rate decimal(16,16)
set @.rate = 0.03333333

-- a random expr2
DECLARE @.expr2 real
set @.expr2 = 23

-- a random expr3
DECLARE @.expr3 real
set @.expr3 = 7

-- First row
select 0 Iter, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,0) Expr1
union
-- Second row
select 1, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,1)
union
-- Third row
select 2, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,2)
union
-- Fourth row
select 3, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,3)
union
-- Fifth row
select 4, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,4)
union
-- Sixth row
select 5, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,5)
union
-- Sixth row
select 6, dbo.AffineDynamic(@.initial, @.rate, @.expr2 - @.expr3,6)


Yields the following:

Iter Expr1
0 25.0000000000000000
1 16.8333320617675780
2 16.5611095428466800
3 16.5520362854003910
4 16.5517330169677730
5 16.5517234802246090
6 16.5517234802246090

Now, note the affinity towards 16.5517234802246090. This is called a point of stability. Any iteration after 5 for A0=25, R=1/30, B= (23-7) = 16 yeilds this number.

To wit:

select 1000 Iter, dbo.AffineDynamic(25, 0.03333333, 16,1000) Expr1

Yields (as expected):

Iter Expr1
1000 16.5517234802246090

Reference:
Discrete Dynamical Modeling, Sandefur, Oxford University Press, 1993, ISBN 0-19-508438-1

|||

Another plain and practical alternative to cursors, and/or esoteric alternates that concentrate on remembering the previous values by storing them on a temporary table that is later listed as show in sample code in the "PreviousRowCalculation" SP.

create table nums(a int, b int)
insert into nums values (0,1)
insert into nums values (1,2)
insert into nums values (2,3)
insert into nums values (3,4)
insert into nums values (4,5)
go
-- drop procedure PreviousRowCalculation
create procedure PreviousRowCalculation
as
begin
set nocount on
create table #tmp (recno int identity, a int, b int, pv int)
insert into #tmp (a,b,pv)
select a,b,0 from nums order by a
declare @.t int,@.c int
set @.t = (select count(*) from #tmp)
set @.c = 1
while @.c <= @.t
begin
update #tmp
set pv = isnull((select pv from #tmp x where x.recno=@.c-1),0) -- expr1
+ (11 + b) - (10 + a) -- + expr2 - expr3
where recno=@.c
set @.c = @.c + 1
end
select * from #tmp
drop table #tmp
end
go

PreviousRowCalculation
go

|||I want to reiterate that this only works with dynamic equations of the form:
A_n = R A_n-1 + B

This is called a First-Order Homogeneous Dynamical Equation

Which is what your request is:

========================
B = expr2 - expr3
expr1_n= [(1/30) * expr1_n-1] + B
========================

This assumes that expr2 - expr3 are constant expressions and not variable values, such as columns in the table.

There are different solutions for other forms - First-Order Non-Homogenous Dynamical Equations, N-Order Homogeneous/Non-Homogenous Dynamical Equations and Systems of Dynamical Equations, just to name a few.

Google:
Logistic Equation
Markov Chains

The point I am trying to make, if there is a distinct causal relationship between values of sequential iterations of a formula, that is:

F(x)_n = G(F(x)_n-1)

There is often an dual formula H(x , k) such that

F_k(x) = H(F(x)₀ , k)

where k is the iteration you want to calculate and F(x)₀ is the intial value, thereby eliminating the need to iterate all the values.

The trick is being able to recognize the form of the system (is it solvable?) and then applying the proper dual.

Ah, the difference between a degree and a certificate!|||

or, using ed's nums table and exprnext = exprPrev +(11 +b) - (10+a)

select cast(null as int) pv, identity(int) id, * into #temp from nums

update #temp set pv = 0 where id = 1

loophere:
update #temp
set pv = calc
from #temp, ( select top 1 curr.ID, prev.pv + (11 + prev.b) - (10+prev.a) calc
from #temp curr inner join #temp prev on curr.id = prev.ID+1 where curr.pv is null ) thecalc
where #temp.ID = thecalc.ID
if @.@.ROWCOUNT <> 0 goto loophere

select * from #temp
drop table #temp

|||sweet.

Just a last note, on Blair last alternate solution is the need to evaluate the expression for the first row alone (if actually needed) since the loop does require to start in "recno 1" + 1 therefore missing its evaluation at row 1.|||the second line is the initial expr1:

update #temp set pv = 0 where id = 1