Help in finding the differences in values across sub groups.

[aakar]

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 :

Collapse | Copy Code
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 :

Collapse | Copy Code
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.

Collapse | Copy Code
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 :

Collapse | Copy Code
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.

Collapse | Copy Code
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.

[SilimSayo]

Export

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

To Excel, Sort by date, insert a column for difference, insert a formula in one cell then drag down the formular for all the other cells.

[Lost User]

do you mean as below?(see last column):

VAN Equity 2011-12-03 00:00:00.000 0.7995 0.7995
VAN Equity 2011-12-04 00:00:00.000 0.7995 0
VAN Equity 2011-12-05 00:00:00.000 0.7 -0.0995
VAN Equity 2011-12-08 00:00:00.000 0.7 0
VAN Equity 2011-12-09 00:00:00.000 0.6 -0.1
VIN Equity 2011-12-03 00:00:00.000 0.1 0.1
VIN Equity 2011-12-04 00:00:00.000 0.2 0.1
VIN Equity 2011-12-05 00:00:00.000 0.7 0.5
VIN Equity 2011-12-08 00:00:00.000 0.7 0
VIN Equity 2011-12-09 00:00:00.000 0.6 -0.1
VAT Equity 2011-12-03 00:00:00.000 0.1 0.1
VAT Equity 2011-12-04 00:00:00.000 0.2 0.1
VAT Equity 2011-12-05 00:00:00.000 0.7 0.5
VAT Equity 2011-12-08 00:00:00.000 0.7 0
VAT Equity 2011-12-09 00:00:00.000 0.6 -0.1

Maybe I have a nice way

[Lost User]

esProc can solve this problem easily. It is just like Excel+SQL, a free tool, see: A Query Language Over-perform SQL [^]. code as below:

A1: =sqlsvr.query("select Name,Dates,[Outstanding Values] from test_table order by Name,Dates")
A2: =A1.derive(:Difference)
A3: =A2.group(Name)
A4: =A3.(~.run(Difference='Outstanding Values'-'Outstanding Values'[-1]))

I can't post a image file,so here are some explains: A1 cell:query some data from database. A2 cell:add a column to A1, named "Difference", just no data. A3 cell:group the data by field "Name" in A2 cell. Here are 3 groups, A4 cell:within every group(i.e. "~"), modify the field "Difference". The algorithm is: "this row" subtract "last row"(i.e. 'Outstanding Values'[-1]) I think esProc is more simple for mass data computation BTW. how to post a image file?