column data operations inside sql

Question

I have a table of increasing dates (with gaps) and corresponding values

date        | value
01.02.2012  | 10
02.02.2012  | 11
03.02.2012  | 33
07.02.2012  | 34
08.02.2012  | 13

I am looking for table of differences for values between dates, like this:

02.02.2012 - 01.02.2012  | 11 - 10 = +1
03.02.2012 - 02.02.2012  | 33 - 11 = +22
07.02.2012 - 03.02.2012  | 34 - 33 = +1
08.02.2012 - 07.02.2012  | 13 - 34 = -21

Now I am load table into excel and make calculations there, but I have feeling there is a possible way of getting it by use of sql commands inside database. Is there any?

UPD. Actually I use access as database system, but If there is a difference I can switch to one that best fit.

Answer 1

Since I don't think analytical functions like Lag are available in access you will need to use a correlated subquery:

SELECT  Date,
        Value,
        NextDate,
        NextValue,
        (NextValue - Value) AS ValueDifference,
        DATEDIFF("D", Date, NextDate) AS DateDifference
FROM    (   SELECT  T.Date,
                    T.Value,
                    (   SELECT  TOP 1 Date
                        FROM    LagTest T1
                        WHERE   T1.Date >  T.Date
                        ORDER BY Date
                    ) AS NextDate,
                    (   SELECT  TOP 1 Value
                        FROM    LagTest T1
                        WHERE   T1.Date >  T.Date
                        ORDER BY Date
                    ) AS NextValue
            FROM    LagTest T
        ) AS T

Answer 2

You didn't specify a DBMS, but this is ANSI SQL that works on most modern DBMS (it's not exactly the output you want, but it does show the difference between two values).

select date, 
       value,
       value - lag(value) over (order by date) as diff
from your_table