SQL select row-wise increase in amount of running total column

Question

Suppose I have a table with columns (DayId, RunningTotal):

DayId    RunningTotal
---------------------
1        25
3        50
6        100
9        200
10       250

How can I select the DayId and the amount the RunningTotal has increased from the previous day? i.e. how can I select:

DayId    DayTotal
---------------------
1        25
3        25
6        50
9        100
10       50

The only current method I know is with a while loop I am trying to factor out. Also, the DayId has no regular rules, just that it is some increasing integer value, but it increases by an irregular amount as shown in the example table.

EDIT: using MS SQL Server 2005

Answer 1

There is probably a more succinct way than this, but try:

select t3.DayId, 
    case when t4.DayId is null then t3.RunningTotal else t3.RunningTotal - t4.RunningTotal end as DayTotal
from (
    select t1.DayId, max(t2.DayId) as PreviousDayId as 
    from MyTable t1
    left outer join MyTable t2 on t2.DayId < t1.DayId
    group by t1.DayId    
) a
inner join MyTable t3 on a.DayId = t3.DayId
left outer join MyTable t4 on a.PreviousDayId = t4.DayId

Answer 2

with cte as (
  select dayid, runningtotal, row_number() over (order by dayid asc) as row_index
  from #the_table
)
select cur.dayid, cur.runningtotal - coalesce(prev.runningtotal, 0) as daytotal
from cte cur
     left join cte prev on prev.row_index = cur.row_index - 1

(I really wish they'd implemented support for the lead and lag functions in SQL Server :|)