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.

Answer this question

Previous Row Calculations (sql server 2000)

Suma Sushilendra MSFT
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.
Binairy

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
Allenvm3
the second line is the initial expr1:

update #temp set pv = 0 where id = 1
cochese

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
gtom808

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
james79
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!
alncnasndf
Thank you my frind for your help but I need give me some examples of the crusors to understand that...

if you can.....
mitov

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
Nik_1982

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
Previous Row Calculations (sql server 2000)

Answer this question