基于有效期计算员工每月过期休假的SQL实现需求

需求说明：基于主表中的有效期信息，计算员工每月的过期休假情况。

表A（员工休假基础信息表） #

存储员工列表、月度允许休假天数及休假有效期，SQL定义及数据如下：

DROP TABLE IF EXISTS #TableA

CREATE TABLE #TableA(
    [EmployeeID] [nvarchar](10) NULL,
    [AllowedLeaveinMonth] [int] NULL,
    [ValidForNumberofMonths] [int] NULL
) ON [PRIMARY]

INSERT INTO #TableA (EmployeeID, AllowedLeaveinMonth, ValidForNumberofMonths) VALUES
(N'E0001', 1, 4),
(N'E0002', 1, 3),
(N'E0003', 1, 3)

表B（员工月度休假记录表） #

存储员工每月的休假记录，SQL定义及数据如下：

DROP TABLE IF EXISTS #TableB
CREATE TABLE #TableB(
    [EmployeeID] [nvarchar](10) NULL,
    [Months] [date] NULL,
    [LeaveTaken_in_Month] [int] NULL
) ON [PRIMARY]


INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0001', '2023-08-01', 0);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0002', '2023-10-01', 1);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0003', '2023-10-01', 1);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0001', '2023-09-01', 1);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0002', '2023-09-01', 0);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0003', '2023-09-01', 2);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0001', '2023-10-01', 0);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0002', '2023-10-01', 1);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0003', '2023-10-01', 1);
INSERT INTO #TableB([EmployeeID], [Months], [LeaveTaken_in_Month]) VALUES (N'E0001', '2023-11-01', 1);

休假规则 #

• E0001每月可获得1天休假，未使用的休假可结转至下月；8月获得的休假可在8、9、10、11月使用，若未使用则在12月过期（有效期4个月）。
• E0002未使用的休假将在11月过期（有效期3个月），其余员工规则以此类推。

预期输出报表 #

内容的提问来源于stack exchange，提问作者Teknas