Does this work?
-- first, some setup.
CREATE TABLE #Amounts (
SomeDate DATETIME,
SomeAmount INT
)
insert into #Amounts values ('2008/09/01',500)
insert into #Amounts values ('2008/09/02',1500)
insert into #Amounts values ('2008/09/03',3500)
-- Setup finished
CREATE TABLE #WorkTable (
RowID INT IDENTITY PRIMARY KEY,
SomeDate DATETIME,
SomeAmount INT
)
INSERT INTO #WorkTable (SomeDate, SomeAmount)
SELECT SomeDate, SomeAmount
FROM #Amounts
ORDER BY SomeDate
SELECT CurrentDay.SomeDate, CurrentDay.SomeAmount-PreviousDay.SomeAmount from #WorkTable PreviousDay inner join #WorkTable CurrentDay
on PreviousDay.RowID = CurrentDay.RowID - 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability