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

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

https://stackoverflow.com/questions/66051919/t-sql-query-for-calculating-sum-of-two-rows-and-substract-sum-of-previous-rows February 05, 2021 at 02:46AM