This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(124.80000000000001, 91%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
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
; 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
Please what if a new record is added for EmployeeId ACKE0001 with BillAmount of 300 and the update still has to be done to update the running balance of the employee to 2700
INSERT EmplyeeBals(EmployeeId, Quota, BillAmount, Balance)
SELECT TOP (1) 'ACKE0001', Quota, 300, Balance -300
FROM EmplyeeBals
WHERE EmployessID = 'ACKE0001'
ORDER BY Id DESC
Thank you Sir, in my particular case, there will be upload of records into the database, so the update will have to be a mass update.
Kindly see below a mass insert where i now have to calculate and update the running balances for the new records using the current balance of 5,000 for ACKE0001 and 8,900 for BARB0001
Any particular reason that you give us your requirements piece by piece, rather than the full story from the beginning?
Maybe the best solution is to define a view over the table that includes the column with the running sum?
So sorry for the piece by piece, I didn't think of this 2nd requirement.
I really need your help with this.
Thank you
You should be able to piece things together from the two solutions I have posted.