Lag/Lead Equivilient

Check out "Common Table Expressions" in the SQL Server 2005 BOL.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Alon Albert" wrote in message
news:MPG.224ae7294894f3f4989681@msnews.microsoft.com...
Oracle has 2 functions that allow access to previous/next rows called
lag & lead. Does Sql Server have equivalent functions?

The problem I am trying to solve here is as follows. I have a table that
represents a graph with X & Y axis.

I need to calculate a weighted average of the values so:

X Y
0 3
10 3
11 5
12 10
13 7
14 3
20 3

I'd like to calculate:
(0*3 + 10*3 + 1*5 + 1*10 + 1*7 + 1*3 + 6*3) / 20

In order to do this, I need to know the delta X of each row from the
previous row. Oracle lag function will allow me to create a simple query
that can return:

X Y DeltaX
0 3
10 3 10
11 5 1
12 10 1
13 7 1
14 3 1
20 3 6

From here, the rest is trivial but I can't find a way of getitng that
without a lag function.

What version of SQL Server are you using?

Is the order of previous/next always determined by the value of X? What
happens when there are two rows where X is the same?

A



"Alon Albert" wrote in message
news:MPG.224ae7294894f3f4989681@msnews.microsoft.com...
> Oracle has 2 functions that allow access to previous/next rows called
> lag & lead. Does Sql Server have equivalent functions?
>
> The problem I am trying to solve here is as follows. I have a table that
> represents a graph with X & Y axis.
>
> I need to calculate a weighted average of the values so:
>
> X Y
> 0 3
> 10 3
> 11 5
> 12 10
> 13 7
> 14 3
> 20 3
>
> I'd like to calculate:
> (0*3 + 10*3 + 1*5 + 1*10 + 1*7 + 1*3 + 6*3) / 20
>
> In order to do this, I need to know the delta X of each row from the
> previous row. Oracle lag function will allow me to create a simple query
> that can return:
>
> X Y DeltaX
> 0 3
> 10 3 10
> 11 5 1
> 12 10 1
> 13 7 1
> 14 3 1
> 20 3 6
>
> From here, the rest is trivial but I can't find a way of getitng that
> without a lag function.
>
>
>
>
>
>