Help in finding the differences in values across sub groups.

See more:

SQL-Server-2005

SQL-Server

Hi
I need help in writing a SQL query that gives the difference in values for two consecutive dates.

My table structure is as follows :

Symbol Name   Dates       Outstanding values
VAN Equity     12/3/2011   0.7995
VAN Equity     12/4/2011   0.7995
VAN Equity     12/5/2011   0.7000
VAN Equity     12/8/2011   0.7000

I want the output in the following form :

Symbol Name   Dates       Difference
VAN Equity     12/3/2011   0.7995
VAN Equity     12/4/2011   0
VAN Equity     12/5/2011   -0.0995
VAN Equity     12/8/2011   0

I came up with the below query.

WITH LHP AS
      (
      SELECT  *, ROW_NUMBER() OVER(ORDER BY Dates ) AS rn
      FROM    test_table as LI
      )
SELECT   mc.[Outstanding Values] - mp.[Outstanding Values],
        mc.[Outstanding Values] , mp.[Outstanding Values]
FROM    LHP mc
inner JOIN    LHP mp
ON    mc.rn = mp.rn - 1

The above query works fine if I have just one set of Symbol Names.
However, if I have my data in the below format :

Symbol Name       Dates            Outstanding Values
VAN Equity   2011-12-03 00:00:00.000   0.7995
VAN Equity   2011-12-04 00:00:00.000   0.7995
VAN Equity   2011-12-05 00:00:00.000   0.7
VAN Equity   2011-12-08 00:00:00.000   0.7
VAN Equity   2011-12-09 00:00:00.000   0.6
VIN Equity   2011-12-03 00:00:00.000   0.1
VIN Equity   2011-12-04 00:00:00.000   0.2
VIN Equity   2011-12-05 00:00:00.000   0.7
VIN Equity   2011-12-08 00:00:00.000   0.7
VIN Equity   2011-12-09 00:00:00.000   0.6
VAT Equity   2011-12-03 00:00:00.000   0.1
VAT Equity   2011-12-04 00:00:00.000   0.2
VAT Equity   2011-12-05 00:00:00.000   0.7
VAT Equity   2011-12-08 00:00:00.000   0.7
VAT Equity   2011-12-09 00:00:00.000   0.6

i.e. multiple sets of Symbol Names distributed across the same set of dates my query gives me the results as shown below : which is not as expected.

No      Outstanding   Outstanding
Name    Values        Values
0.6995   0.7995         0.1
-0.1     0.1            0.2
-0.5995  0.2            0.7995
0.0995   0.7995         0.7
0        0.7            0.7
0.1      0.7            0.6

Any help would be greatly appreciated.