How to Minus Current and Previous Value in SQL Server

How to minus current and previous value in SQL Server

LAG is one option if you are using SQL Server 2012 or later:

SELECT
    Id,
    Amount,
    LAG(Amount, 1, 0) OVER (ORDER BY Id) - Amount AS [Cal-Amount]
FROM yourTable;

If you are using an earlier version of SQL Server, then we can use a self join:

SELECT
    Id,
    Amount,
    COALESCE(t2.Amount, 0) - t1.Amount AS [Cal-Amount]
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t1.Id = t2.Id + 1;

But note that the self join option might only work if the Id values are continuous. LAG is probably the most efficient way to do this, and is also robust to non sequential Id values, so long as the order is correct.

SQL Server - Add or Subtract from Previous Value

The problem of your answer is the Balanced of the previous row is not available when you calculate (SQL-Server 's All-at-once principle). Your question has a hidden requirement of calculating sum of all the previous rows, ordered by date.

Try this (only applied for SQL Server 2012 and beyond)

SELECT
    [Date], Logged, Closed,
    SUM(Logged) OVER (ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    - SUM (Closed) OVER (ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Balance
FROM
    <<Table>>

For previous version:

SELECT
    t.[Date], t.Logged, t.Closed, sub.L - sub.C AS Balanced
FROM
    <<Table>> t
    CROSS APPLY ( SELECT SUM(Logged) AS L, SUM(Closed) AS c FROM <<Table>> WHERE [Date] <= t.[Date]) AS sub

Subtract previous row value to current row

DECLARE @Test TABLE (
    id      int,
    value   int,
    acc_no  int
) 
INSERT @Test(id, value, acc_no)
VALUES
(1, 12, 1),
(2, 14, 1), 
(3, 15, 1),
(4, 10, 2),
(5, 16, 2),
(6, 19, 1),
(7,  7, 3),
(8, 24, 2)

SELECT id, t.value, acc_no, t.value - ISNULL(v.value, 0) AS result
FROM @Test t
    OUTER APPLY (
        SELECT TOP (1) value
        FROM @Test
        WHERE id < t.id
            AND acc_no = t.acc_no
        ORDER by id DESC
    ) v

Subtract current row value by previous row value and get balance amount to current row

You can use window functions:

select
    t.*,
    sum(topupAmount - chargeAmount) over(order by row_num) balanceAmount
from mytable t

Actually by looking at your query it seems like row_num is a generated column, so you likely want:

select
    t.*,
    sum(topupAmount - chargeAmount) over(order by sortDate) balanceAmount
from mytable t

Subtract previous row to calculate column value in MS SQL

You can use window functions and a conditional expression:

select *
from (
    select t.*, lag(date) over(order by date) lag_date
    from mytable t
    where percent_gain < 0
) t
where 
    lag_date is null
    or date = dateadd(day, case when datename(dw, date) = 'Monday' then 3 else 1 end, lag_date)

The subquery filters on rows whose percent_gain is negative, and uses lag() to retrive the date of the "previous" row. Then, the outer query filters again according to the following criteria:

• either the row is the "first" one (ie there is no previous date)

• or the date of the current row one day after the previous date - excepted if the current date is a Monday, in which case the current date should be 3 days later than the previous date

Note that the query does not actually uses columns day, month and year; those look like derived values, that can easily be computed on the fly when needed. Typically, we use datename() to get the day name.

SQL best way to subtract a value of the previous row in the query?

This was the resulting query I went with :)

SELECT SUM(a.StartTime - a.LagEnd) as LostTime 
FROM (SELECT [PlantID], [StartTime], [EndTime], 
             LAG([PlantID]) OVER (ORDER BY PlantID, StartTime) LagPlantID,
             LAG([EndTime]) OVER (ORDER BY PlantID, StartTime) LagEnd 
      FROM MachineRecords) a 
WHERE a.PlantID = a.LagPlantID

To subtract a previous row value in SQL Server 2012

Use a order by with column(s) to get consistent results.

Use lag function to get data from previous row and do the subtraction like this:

with t
as (
    select ROW_NUMBER() over (order by _date) [Sno],
        _Date,
        sum(Payment) Payment
    from DailyPaymentSummary
    group by _date
    )
select *,
    Payment - lag(Payment, 1, 0) over (order by [Sno]) diff
from t;

How can I subtract a previous row in sql?

Assuming you have an ordering column -- say id -- then you can do the following in SQL Server 2012:

select col,
       col - coalesce(lag(col) over (order by id), 0) as diff
from t;

In earlier versions of SQL Server, you can do almost the same thing using a correlated subquery:

select col,
       col - isnull((select top 1 col
                     from t t2
                     where t2.id < t.id
                     order by id desc
                    ), 0)
from t

This uses isnull() instead of coalesce() because of a "bug" in SQL Server that evaluates the first argument twice when using coalesce().

You can also do this with row_number():

with cte as (
      select col, row_number() over (order by id) as seqnum
      from t
     )
select t.col, t.col - coalesce(tprev.col, 0) as diff
from cte t left outer join
     cte tprev
     on t.seqnum = tprev.seqnum + 1;

All of these assume that you have some column for specifying the ordering. It might be an id, or a creation date or something else. SQL tables are inherently unordered, so there is no such thing as a "previous row" without a column specifying the ordering.

Subtract value of a column from previous value of another column in sql

You can use lead() windows function.
Assuming that you are using SQL Server.

Create table and insert statements:

 create table customer_shipping(Customer_ID int, Ship_date date, Supply_date date);
 insert into customer_shipping values(76868773  ,'10/15/2018 0:00','12/10/2018 0:00');
 insert into customer_shipping values(76868773  ,'12/06/2018 0:00','01/31/2019 0:00');
 insert into customer_shipping values(76868773  ,'02/21/2019 0:00','04/18/2019 0:00');
 insert into customer_shipping values(76868773  ,'04/25/2019 0:00','06/20/2019 0:00');
 insert into customer_shipping values(76868773  ,'06/27/2019 0:00','08/22/2019 0:00');
 insert into customer_shipping values(76868773  ,'08/29/2019 0:00','10/24/2019 0:00');
 insert into customer_shipping values(76868773  ,'11/26/2019 0:00','01/21/2020 0:00');
 insert into customer_shipping values(76868773  ,'01/30/2020 0:00','03/26/2020 0:00');
 insert into customer_shipping values(76868773  ,'04/06/2020 0:00','06/01/2020 0:00');
 insert into customer_shipping values(76868773  ,'06/01/2020 0:00','07/27/2020 0:00');
 insert into customer_shipping values(76868773  ,'07/29/2020 0:00','09/23/2020 0:00');

Query:

 select *, datediff(day,supply_date,lead(ship_date)over(partition by customer_id order by ship_date))gap 
 from customer_shipping

Output:

SELECT product, 
       avg_rev, 
       FIRST_VALUE(avg_rev) OVER (ORDER BY product) - avg_rev AS diff_a 
FROM product;