How to use LAG() Function In SQL

The LAG() is a windows function in SQL Server, used to access data from previous row in the same result set without using the self-join method.

Syntax:

LAG(scalar_expression [,offset [, default_value ]]) 
OVER ( PARTITION BY partition_expression     
ORDER BY sort_expression [ASC | DESC])

Explanation:

• The LAG() function is used with the ‘Select’ statement under column list to find the previous value to get the difference with current row value. LAG function has the ‘scalar_expression’ with optional values for offset and default value parameters.
• Scalar_Expression: The scalar expression parameter returns the value based on the ‘offset’ value.
• Offset: This is the value to denote the number of rows from current row to get the value. If no offset value is given, it is default to 1.
• Default_value: Default value is the value to return when the scope of the offset value is beyond the scope of the partition. Null is the default value, if no value for offset is provided.
• In the OVER section of the LAG() function the ‘Order by’ column name is provided to find the correct current and previous row data.

  Partition By: The ‘Partition By’ option is used when we need to partition data rows in a table by a specific column or data value.

Delta computation is a process used in SQL Server to derive the difference between values in a specific column across different rows within the same table. This method is important for analyzing historical data stored in relational databases and allowing users to gain insights by comparing data attributes over time or across various entities.

In this article, We will learn about the Delta computation and the some approaches that help us to Compute a Difference (Delta) Between Two Columns on Different Rows in an SQL Server and so on.