1

Topic: Subtraction of values of lines

Good afternoon.
Prompt, please, as it is possible to build request about subtraction of values of lines.
There is a table:
TableName TotalSpace collection_time
t1 1129032432 2017-09-19 01:00:02.000
t1 1128545656 2017-09-18 01:00:03.000
t1 1127924784 2017-09-17 01:00:02.000
t1 1126651936 2017-09-15 11:54:01.000
t1 1126651936 2017-09-16 01:00:02.000
t2 1063103176 2017-09-19 01:00:02.000
t2 1063069544 2017-09-18 01:00:03.000
t2 1063058040 2017-09-17 01:00:02.000
t2 1063034144 2017-09-16 01:00:02.000
t2 1063008192 2017-09-15 11:54:01.000

It is necessary to count a difference of values TotalSpace between dates.

2

Re: Subtraction of values of lines

beaver06;
LAG

3

Re: Subtraction of values of lines

beaver06, it is possible to anchor the table to itself

DECLARE @source table (TableName varchar (50), TotalSpace int, collection_time datetime)
INSERT INTO @source (TableName, TotalSpace, collection_time)
VALUES (' t1 ', 1129032432, ' 2017-09-19T01:00:02.000 ');
(' t1 ', 1128545656, ' 2017-09-18T01:00:03.000 ');
(' t1 ', 1127924784, ' 2017-09-17T01:00:02.000 ');
(' t1 ', 1126651936, ' 2017-09-15T11:54:01.000 ');
(' t1 ', 1126651936, ' 2017-09-16T01:00:02.000 ');
(' t2 ', 1063103176, ' 2017-09-19T01:00:02.000 ');
(' t2 ', 1063069544, ' 2017-09-18T01:00:03.000 ');
(' t2 ', 1063058040, ' 2017-09-17T01:00:02.000 ');
(' t2 ', 1063034144, ' 2017-09-16T01:00:02.000 ');
(' t2 ', 1063008192, ' 2017-09-15T11:54:01.000 ');
WITH cteXpression (TableName, TotalSpace, collection_time, prev_collection_time)
AS
(
SELECT TableName, TotalSpace, collection_time;
(SELECT MAX (B.collection_time) FROM @source B WHERE B.TableName = A.TableName and B.collection_time <A.collection_time) AS prev_collection_time
FROM @source A
)
SELECT D.TableName, D.TotalSpace, D.collection_time, D.prev_collection_time, D.TotalSpace-COALESCE (E.TotalSpace, 0) AS delta
FROM cteXpression D
LEFT JOIN cteXpression E on D.TableName = E.TableName and D.prev_collection_time=E.collection_time

Result of performance:
TableName, TotalSpace, collection_time, prev_collection_time, delta
t1, 1129032432, 2017-09-19 01:00:02.000, 2017-09-18 01:00:03.000, 486776
t1, 1128545656, 2017-09-18 01:00:03.000, 2017-09-17 01:00:02.000, 620872
t1, 1127924784, 2017-09-17 01:00:02.000, 2017-09-16 01:00:02.000, 1272848
t1, 1126651936, 2017-09-15 11:54:01.000, NULL, 1126651936
t1, 1126651936, 2017-09-16 01:00:02.000, 2017-09-15 11:54:01.000, 0
t2, 1063103176, 2017-09-19 01:00:02.000, 2017-09-18 01:00:03.000, 33632
t2, 1063069544, 2017-09-18 01:00:03.000, 2017-09-17 01:00:02.000, 11504
t2, 1063058040, 2017-09-17 01:00:02.000, 2017-09-16 01:00:02.000, 23896
t2, 1063034144, 2017-09-16 01:00:02.000, 2017-09-15 11:54:01.000, 25952
t2, 1063008192, 2017-09-15 11:54:01.000, NULL, 1063008192

4

Re: Subtraction of values of lines

TaPaK;
Thanks big

5

Re: Subtraction of values of lines

demind74;
On this method-zatroenie lines.
Did not understand yet why.
But too thanks.

6

Re: Subtraction of values of lines

beaver06;
At TaPaK the variant is much more interesting, I that stuck in 2008.