sql server query question!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sql server query question!!!

Author

Topic

ashish908
Starting Member

18 Posts

Posted - 2005-04-28 : 13:53:37

I have a table which is filled with a Network Monitoring Tool with some data. The table contains a column which stores some numeric data (every new record will have an incremented data, this data behaves like a counter)

i want to run a query where i can get the difference between two consecutive data in that column. Morever, the difference should be shown as a seperate column. for e.g. if i have a table as follows

ID values
1 2000
2 2030
3 2090
4 2120

i should get the result as

ID values Difference
1 2000 null
2 2030 30
3 2090 60
4 2120 30

Pls note the difference comes subracting the previous row value from the current row value, in case the previous row does not exist, then it should show null.

Thanks for your help

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-28 : 14:20:35

If there are no missing values in the sequence of numbers in ID column, this should do it:

Select
	a.ID,
	Current_Value	= a.values,
	Prior_Value 	= b.values,
	Difference	= a.values - b.values
from
	MyTable a
	left join
	MyTable b
	on a.ID-1 = b.ID
order by
	a.ID

CODO ERGO SUM

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 14:37:56

[code]INSERT INTO #temp
SELECT 1, 2000 UNION ALL SELECT 2, 2030 UNION ALL SELECT 3, 2090 UNION ALL select 4, 2120

--if you can't guarantee id increments by one(note if you use and identity you can't)
SELECT t.t_id, MAX(t.t_value), MAX(t.t_value) - MAX(t1.t_value) AS t_differnce
FROM #temp t
LEFT OUTER JOIN #temp t1 ON t.t_id > t1.t_id
GROUP BY t.t_id

--if you can gurantee id increments by one(manually assigning the id is the only way)
SELECT t.*, t.t_value - t1.t_value AS t_difference
FROM #temp t
LEFT OUTER JOIN #temp t1 ON t.t_id = t1.t_id + 1
DROP TABLE #temp[/code]

Mike Petanovitch

ashish908
Starting Member

18 Posts

Posted - 2005-04-28 : 15:08:32

Thanks Michael, but i cannot guarantee that the ids are incrementing by 1, the data looks like this
Id Values
1113417923 1129695309
1113418823 1129715440
1113419723 1129767673
1113420623 1129788361
1113421523 1129808738
1113422423 1129867058
1113423322 1129886812
1113424222 1129904858
1113425122 1129923864
1113426022 1129953108
1113426922 1130004656
1113427822 1130036378
1113428722 1130104244
1113429622 1130127632
1113430522 1130151946

ashish908
Starting Member

18 Posts

Posted - 2005-04-28 : 15:11:18

Also, Mike how can i write this statement,
------------------------------------------------------------------------------------------
INSERT INTO #temp
SELECT 1, 2000 UNION ALL SELECT 2, 2030 UNION ALL SELECT 3, 2090 UNION ALL select 4, 2120
------------------------------------------------------------------------------------------

The table contains more than 10000 rows, how could i write 10000 union all. I dont understand

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-04-28 : 15:35:06

If you create a new table (or a temp table) with a "diff" column in addition to the "id" and "values" columns, then you can also try this after populating that new table (make sure the records are in the correct order):

DECLARE @diff int, @prev_value int
SET @diff = 2000 -- or whatever the first value is
SET @prev_value = 2000 -- or whatever the first value is

UPDATE YourTable
SET @diff = diff = value - @prev_value, @prev_value = value

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 15:51:48

The #temp table is a sample table to illustrate the select statements I have...its not part of the solution.

All you need is this:(but change the column names to match yours)

SELECT t.t_id, MAX(t.t_value), MAX(t.t_value) - MAX(t1.t_value) AS t_difference
FROM #temp t
LEFT OUTER JOIN #temp t1 ON t.t_id > t1.t_id
GROUP BY t.t_id

Also Note: not sure how the performance will be on this, might be faster to use a subquery or derived table.

Mike Petanovitch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources