How to get running difference with SQL?

Hi, Experts:
I have a question about getting running difference. The difference is
compared with previous week, if no data for previous week, the difference is
null. Any suggestions? Thanks much!
id week weight diff
1 1 100 0
1 2 102 -2
1 3 99 3
2 1 103 0
2 3 102 null
I think this will do what you describe. The one difference is that
the value for week 1 follows the rule specified "if no data for
previous week, the difference is null", while the data shown does not
reflect that. A CASE expression could be used to enforce that week 1
would get zero, but I chose not to complicate the query that much.
SELECT d, week, weight,
(SELECT A.weight - B.weight
FROM Whatever as B
WHERE A.d = B.d
AND A.week = B.week + 1)
FROM Whatever as A
Roy Harvey
Beacon Falls, CT
On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
<luvgreen@.discussions.microsoft.com> wrote:

>Hi, Experts:
>I have a question about getting running difference. The difference is
>compared with previous week, if no data for previous week, the difference is
>null. Any suggestions? Thanks much!
>id week weight diff
>1 1 100 0
>1 2 102 -2
>1 3 99 3
>2 1 103 0
>2 3 102 null
|||Thank you so very much. It is really helpful!!
"Roy Harvey" wrote:

> I think this will do what you describe. The one difference is that
> the value for week 1 follows the rule specified "if no data for
> previous week, the difference is null", while the data shown does not
> reflect that. A CASE expression could be used to enforce that week 1
> would get zero, but I chose not to complicate the query that much.
> SELECT d, week, weight,
> (SELECT A.weight - B.weight
> FROM Whatever as B
> WHERE A.d = B.d
> AND A.week = B.week + 1)
> FROM Whatever as A
> Roy Harvey
> Beacon Falls, CT
> On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
> <luvgreen@.discussions.microsoft.com> wrote:
>