sql server - T-SQL Query for Calculating Sum of two rows and Substract sum of Previous rows

Question

I was wondering how this Query can be written. I tried with Rank and Preceeding in SQL Server 2019 but could not come up with the result. I have the following table structure and data:

USE [tempdb]
GO

CREATE TABLE #Orders(
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [Ordercount] [int] NULL,
    [OrderDate] [datetime] NULL
) ON [PRIMARY]
GO

   SET IDENTITY_INSERT #Orders ON 
   GO


     INSERT #Orders ([RowID], [Ordercount], [OrderDate]) VALUES (1, 576, CAST(N'2021-02-04T09:21:22.620' 
     AS DateTime))

     INSERT #Orders ([RowID], [Ordercount], [OrderDate]) VALUES (2, 632, CAST(N'2021-02-04T09:21:38.770' 
     AS DateTime))

    INSERT #Orders ([RowID], [Ordercount], [OrderDate]) VALUES (3, 788, CAST(N'2021-02-04T09:21:51.040' 
    AS DateTime))

    INSERT #Orders ([RowID], [Ordercount], [OrderDate]) VALUES (4, 976, CAST(N'2021-02-04T09:22:09.637' AS 
    DateTime))

    INSERT #Orders ([RowID], [Ordercount], [OrderDate]) VALUES (5, 1032, CAST(N'2021-02-04T09:22:34.873' 
    AS DateTime))

    INSERT #Orders ([RowID], [Ordercount], [OrderDate]) VALUES (6, 1200, CAST(N'2021-02-04T09:22:47.853' 
    AS DateTime))

   SET IDENTITY_INSERT #Orders OFF


   Select * from #Orders
   Order by OrderDate Desc 

What I need is the Sum of the first two rows count - Sum of the preceeding row count in a separate column, for example in the data above it will be

Select (1200+1032) - (976+788) `enter code here`--468
Select (976+788) - (632+576) --556

Is it possible to write a query with Lag and Rank and other functions or other methods.

Thanks much Ak

question from:https://stackoverflow.com/questions/66051919/t-sql-query-for-calculating-sum-of-two-rows-and-substract-sum-of-previous-rows

Answer 1

Use three LAG functions together. I assume the ordering is as you wrote at the bottom of your code. Not sure what you want to do for the first 3 rows, I have set a default of zero: the syntax is: LAG(expression, offset, default) OVER (...

Select *,
    MySum = Ordercount
        + LAG(OrderCount, 1, 0) OVER (ORDER BY OrderDate DESC)
        - LAG(OrderCount, 2, 0) OVER (ORDER BY OrderDate DESC)
        - LAG(OrderCount, 3, 0) OVER (ORDER BY OrderDate DESC)
from #Orders