Retrieve value from the row above

Question

How can I retrieve the FinalBalance value from the previous row and add it to the Total value in the current row, to get the new FinalBalance value for each row? The table structure and desired outcome are shown in the image below. Each row's TransDate is unique.

The SQL code to create the table and insert values is:

CREATE TABLE [dbo].[DalyBalances](
[TransDate] [date] NOT NULL,
[Others] [float] NOT NULL,
[LoansDisbursed] [float] NOT NULL,
[RepaymentReceived] [float] NOT NULL,
[BookletSales] [float] NOT NULL,
[InterestPaidOut] [float] NOT NULL,
[Total] [float] NULL,
[FinalBalance] [float] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[DalyBalances] ([TransDate], [Others], [LoansDisbursed], [RepaymentReceived], [BookletSales], [InterestPaidOut], [Total], [FinalBalance]) VALUES (CAST(N'2024-01-01' AS Date), 20000, 0, 0, 0, 0, 20000, 2000)
GO
INSERT [dbo].[DalyBalances] ([TransDate], [Others], [LoansDisbursed], [RepaymentReceived], [BookletSales], [InterestPaidOut], [Total], [FinalBalance]) VALUES (CAST(N'2024-04-12' AS Date), 0, 0, 0, 0, 0, 0, 2000)
GO
INSERT [dbo].[DalyBalances] ([TransDate], [Others], [LoansDisbursed], [RepaymentReceived], [BookletSales], [InterestPaidOut], [Total], [FinalBalance]) VALUES (CAST(N'2024-04-14' AS Date), 0, 1200, 200, 0, 10, 1400, 3400)
GO
INSERT [dbo].[DalyBalances] ([TransDate], [Others], [LoansDisbursed], [RepaymentReceived], [BookletSales], [InterestPaidOut], [Total], [FinalBalance]) VALUES (CAST(N'2024-04-15' AS Date), 0, 400, 0, 300, 10, 700, 4100)
GO

Answer 1

Hi @Michael M.M. D'Angelo

To retrieve the FinalBalance value from the previous row, you could use the LAG() function.

Try this:

SELECT *,[Total] + LAG([FinalBalance],1,0)OVER(ORDER BY [TransDate]) AS New_FinalBalance
FROM [DalyBalances]

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".