{"id":10541,"date":"2020-04-09T09:45:22","date_gmt":"2020-04-09T12:45:22","guid":{"rendered":"http:\/\/kb.elipse.com.br\/en\/?p=10541"},"modified":"2020-04-09T09:45:22","modified_gmt":"2020-04-09T12:45:22","slug":"kb-106987-subtracting-record-values-in-a-table-field","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/kb-106987-subtracting-record-values-in-a-table-field\/","title":{"rendered":"KB-106987: Subtracting record values in a table Field."},"content":{"rendered":"

Question:<\/h3>\n

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?<\/p>\n

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

Solution:<\/h3>\n

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

Syntax<\/h4>\n

LAG (scalar_expression [,offset] [,default])
\n<\/em>\u00a0 \u00a0 \u00a0 \u00a0OVER ( [ partition_by_clause ] order_by_clause )<\/em>
\n<\/em><\/p>\n

Arguments<\/h4>\n

scalar_expression<\/em>
\nThis is the value to be returned based on the specified shift. This is an any-type expression, which returns a single value (scalar).\u00a0 scalar_expression<\/em> can’t be an analytic function.<\/p>\n

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

default<\/em>
\nThis is the value to be returned when\u00a0 offset<\/em> is beyond the partition scope. If a default value is not specified, then it returns NULL. default<\/em> can be a column, a subquery, or any other expression, but it can’t be an analytic function. Therefore,\u00a0 default<\/em> must be compatible to scalar_expression.<\/p>\n

OVER ( [ partition_by_clause ] order_by_clause )<\/em>
\npartition_by_clause<\/em><\/span> 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.<\/p>\n

order_by_clause<\/em><\/span> determines the data order before the function is applies. If partition_by_clause<\/em> 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)<\/a>.<\/p>\n

<\/h3>\n

Example:<\/h3>\n

1. Firstly: create the table:<\/h4>\n
CREATE TABLE<\/span> Hist_1 (\r\nE3TimeStamp datetime<\/span> not null,\r\nSE_10F03 int<\/span> not null,\r\nP1F01 int<\/span> not null,\r\nP2F01 int<\/span> not null\r\n)<\/pre>\n
2. Then: insert values:<\/h4>\n
INSERT INTO<\/span> Hist_1\r\n(E3TimeStamp,\r\nSE_10F03,\r\nP1F01,\r\nP2F01)\r\nVALUES<\/span>\r\n('17-01-2020 15:34:00'<\/span>, 425, 182, 345),\r\n('17-01-2020 15:38:00'<\/span>, 445, 189, 356),\r\n('17-01-2020 15:42:00'<\/span>, 465, 199, 360),\r\n('17-01-2020 15:48:00'<\/span>, 485, 203, 394),\r\n('17-01-2020 15:52:00'<\/span>, 502, 206, 454),\r\n('17-01-2020 15:56:00'<\/span>, 506, 209, 480)<\/pre>\n
3. After that: return previously inserted data:<\/h4>\n
SELECT<\/span> * FROM<\/span> Hist_1<\/pre>\n
\"\"<\/a><\/p>\n
4. Finally: use LAG function to retrieve the difference between registers:<\/h4>\n
SELECT<\/span> Hist_1.E3TimeStamp,\r\n\u00a0 \u00a0 \u00a0 \u00a0 ISNULL<\/span>((Hist_1.SE_10F03 - LAG<\/span>(Hist_1.SE_10F03,1) OVER<\/span> (ORDER BY<\/span> Hist_1.E3TimeStamp ASC<\/span>)) ,0) as<\/span> SE_10F03,\r\n\u00a0 \u00a0 \u00a0 \u00a0 ISNULL<\/span>((Hist_1.P1F01 - LAG<\/span>(Hist_1.P1F01,1) OVER<\/span> (ORDER BY<\/span> Hist_1.E3TimeStamp ASC<\/span>)) ,0) as<\/span> P1F01,\r\n\u00a0 \u00a0 \u00a0 \u00a0 ISNULL<\/span>((Hist_1.P2F01 - LAG<\/span>(Hist_1.P2F01,1) OVER<\/span> (ORDER BY<\/span> Hist_1.E3TimeStamp ASC<\/span>)) ,0) as<\/span> P2F01\r\nFROM<\/span> Hist_1\r\nORDER BY<\/span> Hist_1.E3TimeStamp ASC<\/span><\/pre>\n
\"\"<\/a><\/p>\n
Another possible solution would be to use the following SQL syntax:<\/p>\n
SELECT   <\/span>E3TimeStamp\r\n        ,[SE_10F03]\r\n        ,[SE_10F03] - (\r\n          SELECT<\/span> TOP<\/span> 1 [SE_10F03]\r\n          FROM<\/span> [Hist_1] b\r\n          WHERE<\/span> a.E3TimeStamp > b.E3TimeStamp\r\n          ORDER<\/span> BY<\/span> b.E3TimeStamp DESC<\/span>) as<\/span> Diff\r\nFROM<\/span> [Hist_1] a<\/pre>\n

\nRelated Articles:<\/strong><\/h3>\n
\n