Database

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.

Quote:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE sp_add_event_log
(
IN param_event_log_category varcha' at line 1

DROP PROCEDURE IF EXISTS `sp_add_event_log`;

CREATE OR REPLACE PROCEDURE sp_add_event_log
     (
				IN param_event_log_category							varchar(255), 
				IN param_event_log_reference						varchar(255),
				IN param_event_log_user									varchar(255), 
				IN param_event_log_machine_name					varchar(255), 
				IN param_event_log_os_version						varchar(255), 
				IN param_event_log_windows_user					varchar(255),
				IN param_event_log_windows_user_domain	varchar(255), 
				IN param_event_log_processor_count			int, 
				IN param_event_log_stack_trace					text, 
				IN param_event_log_working_set					int, 
				IN param_event_log_tick_count						int, 
				IN param_event_log_current_culture			varchar(255), 
				IN param_event_log_date_time_format			varchar(255), 
				IN param_event_log_number_format				varchar(255), 
				IN param_event_log_language_name				varchar(255), 
				IN param_event_log_text									text
     )
BEGIN 
	INSERT INTO students
	(
		event_log_category,
		event_log_reference,
		event_log_user,
		event_log_machine_name,
		event_log_os_version,
		event_log_windows_user,
		event_log_windows_user_domain,
		event_log_processor_count,
		event_log_stack_trace,
		event_log_working_set,
		event_log_tick_count,
		event_log_current_culture,
		event_log_date_time_format,
		event_log_number_format,
		event_log_language_name,
		event_log_text
	)
	VALUES 
	( 
		param_event_log_category, 
		param_event_log_reference, 
		param_event_log_user, 
		param_event_log_machine_name, 
		param_event_log_os_version, 
		param_event_log_windows_user,
		param_event_log_windows_user_domain, 
		param_event_log_processor_count, 
		param_event_log_stack_trace, 
		param_event_log_working_set, 
		param_event_log_tick_count, 
		param_event_log_current_culture, 
		param_event_log_date_time_format, 
		param_event_log_number_format, 
		param_event_log_language_name, 
		param_event_log_text
	) ; 
END