Updating running balance in SQL

Michael M.M. D'Angelo 176 Reputation points
2024-02-08T15:35:07.85+00:00

How can I update the Balance in a SQL table based on the BillAmount , EmployeeId and Id fields? I want to update the balance of every record to be less by the BillAmount of that record when compared to the immediate previous record for the same EmployeeId. Also, the Quota should be used as the basis for the first deduction per EmployeeId. The table structure and desired outcome are shown in the image below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmplyeeBals](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [char](15) NOT NULL,
	[Quota] [decimal](18, 4) NOT NULL,
	[BillAmount] [decimal](18, 4) NOT NULL,
	[Balance] [decimal](18, 4) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[EmplyeeBals] ON 
GO
INSERT [dbo].[EmplyeeBals] ([Id], [EmployeeId], [Quota], [BillAmount], [Balance]) VALUES (1, N'ACKE0001', CAST(5000.0000 AS Decimal(18, 4)), CAST(200.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)))
GO
INSERT [dbo].[EmplyeeBals] ([Id], [EmployeeId], [Quota], [BillAmount], [Balance]) VALUES (2, N'ACKE0001', CAST(5000.0000 AS Decimal(18, 4)), CAST(300.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)))
GO
INSERT [dbo].[EmplyeeBals] ([Id], [EmployeeId], [Quota], [BillAmount], [Balance]) VALUES (3, N'ACKE0001', CAST(5000.0000 AS Decimal(18, 4)), CAST(400.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)))
GO
INSERT [dbo].[EmplyeeBals] ([Id], [EmployeeId], [Quota], [BillAmount], [Balance]) VALUES (4, N'ACKE0001', CAST(5000.0000 AS Decimal(18, 4)), CAST(500.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)))
GO
INSERT [dbo].[EmplyeeBals] ([Id], [EmployeeId], [Quota], [BillAmount], [Balance]) VALUES (5, N'ACKE0001', CAST(5000.0000 AS Decimal(18, 4)), CAST(600.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)))
GO
INSERT [dbo].[EmplyeeBals] ([Id], [EmployeeId], [Quota], [BillAmount], [Balance]) VALUES (7, N'BAC00023', CAST(5000.0000 AS Decimal(18, 4)), CAST(450.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)))
GO
INSERT [dbo].[EmplyeeBals] ([Id], [EmployeeId], [Quota], [BillAmount], [Balance]) VALUES (8, N'BAC00023', CAST(5000.0000 AS Decimal(18, 4)), CAST(1200.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)))
GO
SET IDENTITY_INSERT [dbo].[EmplyeeBals] OFF
GO

User's image

Developer technologies | Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 122K Reputation points MVP Volunteer Moderator
    2024-02-08T22:39:43.1633333+00:00
    ; WITH CTE AS (
       SELECT Balance, Quota - SUM(BillAmount) OVER (PARTITION BY EmployeeId ORDER BY Id
                                                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NewBalance
       FROM   EmplyeeBals
    )
    UPDATE CTE
    SET    Balance = NewBalance
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.