SQL中到期日与付款日对比的特定金额查询问题及解决方案

需求说明 #

需查询特定合同的账务数据，核心需对比到期日（DueDate）与付款日（PaymentDate），实现以下效果：

• 查询ContractID=156时，移除结果中12月的逾期行，保留其余有效数据
• ContractID=2254的查询结果为无逾期时的预期效果，可作为参照

初始问题查询代码 #

以下查询未处理逾期行过滤，导致ContractID=156结果中包含逾期数据：

SELECT ContractID, DueDate, PaymentDate, OutstandingBalance [Outstanding], MonthlyAmortization, 
row_number() over(partition by ContractID order by DueDate desc) as rn 
FROM [div].[ClientsLedger] 
WHERE DueDate BETWEEN '1/1/2000' AND '1/31/2023' AND PaymentDate IS NOT NULL AND ContractID = 156

无逾期预期效果参照代码 #

ContractID=2254的查询结果符合无逾期时的预期，代码如下：

SELECT ContractID, DueDate, PaymentDate, OutstandingBalance [Outstanding], MonthlyAmortization, 
row_number() over(partition by ContractID order by DueDate desc) as rn 
FROM [div].[ClientsLedger] 
WHERE DueDate BETWEEN '1/1/2000' AND '1/31/2023' AND PaymentDate IS NOT NULL AND ContractID = 2254

解决方案代码 #

通过拆分首行做日期校验，再用UNION ALL合并其余历史数据的方式，实现过滤逾期行的需求：

SELECT TOP 1 ContractID, DueDate, 
CASE 
WHEN DueDate >= PaymentDate THEN PaymentDate
ELSE NULL
END AS PaymentDate,
OutstandingBalance [Outstanding], MonthlyAmortization, 
row_number() over(partition by ContractID order by DueDate desc) as rn 
FROM [div].[ClientsLedger] 
WHERE 
DueDate BETWEEN '1/1/2000' AND '1/31/2023' 
AND PaymentDate IS NOT NULL
AND ContractID = 156
UNION ALL
SELECT ContractID, DueDate, PaymentDate, OutstandingBalance [Outstanding], MonthlyAmortization, row_number() over(partition by ContractID order by DueDate desc) as rn FROM [div].[ClientsLedger]
WHERE 
DueDate BETWEEN '1/1/2000' AND DATEADD(MONTH, -2, '1/31/2023')
AND PaymentDate IS NOT NULL
AND ContractID = 156

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