Subtract record values in a table Field in Elipse E3.

Question:

There is a table field that receives the tag whose value is the total sum of a certain variable in the system; however, I need to subtract these record values in the Field, not to sum them. How can I calculate this?

NOTE: The solution proposed in this article, which uses LAG analytic function (Transact-SQL), applies to SQL Server 2012 and higher; for other versions, or other databases, there is an alternative solution, published at the end of this article.

Solution:

In this case, we can use the analytic function LAG (Transact-SQL), which accesses data from a previous line in the same set of results. This function is a new feature from SQL Server 2012; you can use it in a SELECT instruction, which compares values on the current line to values on the previous one.

Syntax

LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

scalar_expression
This is the value to be returned based on the specified shift. This is an any-type expression, which returns a single value (scalar). scalar_expression can’t be an analytic function.

offset
This is the number of lines behind the current line from where you can retrieve a value. If not specified, default is 1. offset can be a column, a subquery, or any other expression evaluated for a positive integer, or then implicitly converted to bigint. However, offset can’t have a negative value or be an analytic function.

default
This is the value to be returned when offset is beyond the partition scope. If a default value is not specified, then it returns NULL. default can be a column, a subquery, or any other expression, but it can’t be an analytic function. Therefore, default must be compatible to scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause divides the set of results produced by the FROM clause in partitions to which the function is applied. If not specified, then the function will handle all lines in the results set as a single group.

order_by_clause determines the data order before the function is applies. If partition_by_clause is specified, then it will determine the data order in the partition. order_by_clause is mandatory. For further information, check OVER Clause (Transact-SQL).

Example:

1. Firstly: create the table:

CREATE TABLE Hist_1 (
E3TimeStamp datetime not null,
SE_10F03 int not null,
P1F01 int not null,
P2F01 int not null
)

2. Then: insert values:

INSERT INTO Hist_1
(E3TimeStamp,
SE_10F03,
P1F01,
P2F01)
VALUES
('17-01-2020 15:34:00', 425, 182, 345),
('17-01-2020 15:38:00', 445, 189, 356),
('17-01-2020 15:42:00', 465, 199, 360),
('17-01-2020 15:48:00', 485, 203, 394),
('17-01-2020 15:52:00', 502, 206, 454),
('17-01-2020 15:56:00', 506, 209, 480)

3. After that: return previously inserted data:

SELECT * FROM Hist_1

4. Finally: use LAG function to retrieve the difference between registers:

SELECT Hist_1.E3TimeStamp,
        ISNULL((Hist_1.SE_10F03 - LAG(Hist_1.SE_10F03,1) OVER (ORDER BY Hist_1.E3TimeStamp ASC)) ,0) as SE_10F03,
        ISNULL((Hist_1.P1F01 - LAG(Hist_1.P1F01,1) OVER (ORDER BY Hist_1.E3TimeStamp ASC)) ,0) as P1F01,
        ISNULL((Hist_1.P2F01 - LAG(Hist_1.P2F01,1) OVER (ORDER BY Hist_1.E3TimeStamp ASC)) ,0) as P2F01
FROM Hist_1
ORDER BY Hist_1.E3TimeStamp ASC

Another possible solution would be to use the following SQL syntax:

SELECT   E3TimeStamp
        ,[SE_10F03]
        ,[SE_10F03] - (
          SELECT TOP 1 [SE_10F03]
          FROM [Hist_1] b
          WHERE a.E3TimeStamp > b.E3TimeStamp
          ORDER BY b.E3TimeStamp DESC) as Diff
FROM [Hist_1] a

KB-106987: Subtracting record values in a table Field.