[thelist] sql - subtract todays value from yesterdays

[Richard Bennett]

Hi,

I'm a bit stuck with an MS SQL query:

Table:

Date                Amount
09/12/2003            0
10/12/2003     1000
11/12/2003     1100
12/12/2003     1200
13/12/2003     1300
14/12/2003     1300

Result I need:

Date                Amount    Used
09/12/2003           0               0
10/12/2003     1000        1000
11/12/2003     1100          100
12/12/2003     1200          100
13/12/2003     1300          100
14/12/2003     1300              0

What I have is this:
SELECT     [Date] , [Amount],
[Amount] - CASE WHEN [Date] = DATEADD(day, -1, [Date]) 
THEN [Amount] ELSE 0 END AS 'Used',
FROM         table

Which doesn't get yesterdays 'Amount' value.

Any ideas?

Thanks,
Richard.