Updating running balance in SQL

Question

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

Answer 1

; 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