Sometimes, we need to calculate the difference between the values of the current row and the previous row in a table or you can say compare the values of current row with previous row. This can be easily done using the LAG function.
SQL Server LAG function provides access to a row at a given physical offset that comes before the current row.
As shown below, we have a sample table named TransactionHistory.
Below are the CREATE TABLE and INSERT scripts to insert some sample data into table.
CREATE TABLE TransactionHistory ( TranID INT PRIMARY KEY, TranDate DATE, Amount DECIMAL(10, 2) ); INSERT INTO TransactionHistory (TranID, TranDate, Amount) VALUES (1, '2024-02-15', 50.00), (2, '2024-03-14', 100.00), (3, '2024-03-18', 180.00), (4, '2024-04-04', 290.00), (5, '2024-05-16', 350.00);
Calculate the Difference Between Current and Previous Row Values
The following query calculates the difference between the Amount of the current row and the Amount of the previous row.
SELECT TranID, TranDate, Amount, LAG(Amount, 1) OVER (ORDER BY TranDate) AS PreviousDayAmount, (Amount - LAG(Amount, 1) OVER (ORDER BY TranDate)) AS AmountDifference FROM TransactionHistory;
Below is the output of above query, and you can see it returns the difference between the Amount of the current row and the Amount of the previous row.
Let’s understand the working of above query:
- LAG(Amount, 1) OVER (ORDER BY TranDate) gets the Amount of the previous row based on the TranDate order.
- (Amount – LAG(Amount, 1) OVER (ORDER BY TranDate)) calculates the difference between the current row’s Amount and the previous row’s Amount.
So, for TranID 1, there is no previous row, so PreviousDayAmount and AmountDifference are NULL.
For TranID 2, the PreviousDayAmount is 100, so the AmountDifference will be (100-50) that is 50.
Similarly, it calculates the difference between the current row’s amount and the previous row’s amount for subsequent rows.
Read More..
