Compute a Difference (Delta) Between Two Columns on Different Rows in SQL Server

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.

• Delta computation is a process of subtracting the value of one column from another within the same table but on different rows. This process is an important method, used in scenarios where historical data is stored in relational database tables.
• Using this method different insights are derived by comparing the values of specific data attributes over time or between various entities.
• The Delta computation helps to compute differences between columns on different rows. This method provides valuable insights to make informed decisions and facilitates a deeper understanding of dynamic data, which changes over time.

We will Computing a difference between two columns on different rows can be done using the window function, LAG() as below:

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.

Example 1

To explain DELTA computation using LAG() method with an example, we are using the below sample table and data:

Let’s Create Table Script:

CREATE TABLE [dbo].[Sales]
(    
    [SalesID] [int] IDENTITY(1,1) NOT NULL,
    [SalesDate] [Date] NULL,
    [TotalSales] [Numeric] NULL
) 
ON [PRIMARY]
GO

The above is the create table script to create ‘Sales‘ table for storing daily sales data.

INSERT INTO Sales VALUES ('04/01/2024',12000)
INSERT INTO Sales VALUES ('04/02/2024',11400)
INSERT INTO Sales VALUES ('04/03/2024',12200)
INSERT INTO Sales VALUES ('04/04/2024',12800)
INSERT INTO Sales VALUES ('04/05/2024',11900)
INSERT INTO Sales VALUES ('04/06/2024',13100)
INSERT INTO Sales VALUES ('04/07/2024',12800)
INSERT INTO Sales VALUES ('04/08/2024',12000)
INSERT INTO Sales VALUES ('04/09/2024',14200)
INSERT INTO Sales VALUES ('04/10/2024',12600)
INSERT INTO Sales VALUES ('04/11/2024',13200)
INSERT INTO Sales VALUES ('04/12/2024',12900)

The below example gives the difference between current sales value with the previous day’s value. The LAG() function takes the ‘TotalSales‘ value and then uses ‘Order by SalesDate‘ to compare sales data from current to the previous date.

Query:

In the below table named Sales which stores daily sales data, compute the daily change in sales by comparing each day’s sales with the previous day’s sales. The objective is to calculate the difference (delta) between the TotalSales values of consecutive days to analyze sales trends and variations over time.

SELECT 
    SalesDate, 
    TotalSales,
    LAG (TotalSales) OVER (ORDER BY SalesDate) - TotalSales AS DailyChange
FROM 
    Sales;

Output:

Explanation: The above output displays the sales date, total sales and the daily change value which shows the difference with the previous days’s sales. Here the current row sales data is compared with the previous day sales and the difference of sales amount is displayed using the LAG() function.

Example 2

Let’s saw another example for LAG() function with ‘Partition by’ option is given below:

For this example we have a sample table called MonthlySales and the create table script is given below.

CREATE TABLE [dbo].[MonthlySales](    [MonthlySalesID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [VARCHAR] (500),    
[SalesMonth] [int] NULL,    
[SalesYear] [int] NULL,    
[TotalMonthlySales] [Numeric] NULL) 
ON [PRIMARY]
GO

Let’s Insert sample sales data for few items for 3 months.

Query: Below is the query to find the difference between current month sales with the previous month sales using LAG() Function with Partition by parameter:

SELECT     ProductName, 
SalesMonth, 
TotalMonthlySales As CurrentMonthSales,
LAG(TotalMonthlySales,1) OVER (Partition by ProductName  ORDER BY SalesMonth)   AS PreviousMonthSale,
TotalMonthlySales - LAG(TotalMonthlySales,1) OVER (Partition by ProductName  ORDER BY SalesMonth) AS MonthlyChange
FROM MonthlySales;

Output:

Explanation: In the output above for the LAG() function with ‘Partition By‘ parameter, we can see the Product Name, Month, Monthly sales, Previous Month Sales and Monthly Change. The sales value is partitioned by product names, and so for each product how the sales has performed over the months is displayed for analysis.

Overall, In this article we have discussed about the DELTA method in SQL Server, which is used to compute a difference between two Columns on different rows in SQL Server. The purpose of this feature in SQL Server is explained with details. The LAG() windows function method is explained in detail with example queries. Also, the article provides sample table, sample data and the output of the query for understanding and how the example works.