Access 连接SQL SERVER 自制的管理管理系统，欢迎来使用

下载地址 https://wwrj.lanzouw.com/iXOR62m1t0za


-- 步骤1: 创建利率有效期临时表
SELECT
a.LNO,
a.LPR,
a.Sdd AS StartDate,
-- ISNULL((SELECT MIN(Sdd) FROM TbInterestRate b WHERE b.Sdd > a.Sdd), '9999-12-31') AS EndDate
dateadd(d,-1,ISNULL((SELECT MIN(Sdd) FROM TbInterestRate b WHERE b.Sdd > a.Sdd), '9999-12-31')) AS EndDate
INTO #InterestRates
FROM TbInterestRate a;
-- 步骤2: 生成贷款与利率的交集时间段
SELECT
L.LID,
L.Cid,
L.Cno,
L.AMT,
L.S_dd,
L.E_DD,
IR.LPR,
PeriodStart = CASE WHEN L.S_dd > IR.StartDate THEN L.S_dd ELSE IR.StartDate END,
PeriodEnd = CASE WHEN L.E_DD < IR.EndDate THEN L.E_DD ELSE IR.EndDate END
INTO #LoanPeriods
FROM TbLoan L
CROSS APPLY (
SELECT *
FROM #InterestRates
WHERE StartDate <= L.E_DD
AND EndDate > L.S_dd
) IR;
-- 步骤3: 按自然季度分割时间段（递归CTE）
WITH SplitQuarter AS (
SELECT
LID,Cid,Cno,AMT,S_dd,E_DD,LPR,
PeriodStart,
PeriodEnd,
QStart = DATEADD(QQ, DATEDIFF(QQ, 0, PeriodStart), 0),
QEnd = DATEADD(DD, -1, DATEADD(QQ, DATEDIFF(QQ, 0, PeriodStart) + 1, 0))
FROM #LoanPeriods
UNION ALL
SELECT
LID,Cid,Cno,AMT,S_dd,E_DD,LPR,
PeriodStart = DATEADD(QQ, 1, QStart),
PeriodEnd,
DATEADD(QQ, 1, QStart),
DATEADD(DD, -1, DATEADD(QQ, DATEDIFF(QQ, 0, DATEADD(QQ, 1, QStart)) + 1, 0))
FROM SplitQuarter
WHERE DATEADD(QQ, 1, QStart) <= PeriodEnd
)
SELECT
LID,Cid,Cno,AMT,S_dd,E_DD,LPR,
StartDate = CASE WHEN PeriodStart > QStart THEN PeriodStart ELSE QStart END,
EndDate = CASE WHEN QEnd < PeriodEnd THEN QEnd ELSE PeriodEnd END
INTO #FinalPeriods
FROM SplitQuarter
WHERE QStart <= PeriodEnd
OPTION (MAXRECURSION 0);
-- 步骤4: 计算利息和生成序号
SELECT
序号 = ROW_NUMBER() OVER(PARTITION BY LID, DATEPART(QQ, StartDate) ORDER BY StartDate),
LID,
Cid,
Cno,
AMT,
S_dd,
E_DD,
季度 = DATEPART(QQ, StartDate),
起算日期 = StartDate,
止算日期 = EndDate,
计息时间 = DATEDIFF(DAY, StartDate, EndDate) + 1,
[1年期LPR利率] = LPR,
利息 = ROUND(AMT * LPR / 100 * (DATEDIFF(DAY, StartDate, EndDate) + 1) / 365, 6),
备注 = CASE WHEN EXISTS(SELECT 1 FROM TbInterestRate
WHERE Sdd = StartDate) THEN '利率调整' ELSE '' END
FROM #FinalPeriods
ORDER BY LID, 起算日期;
-- 清理临时表
DROP TABLE #InterestRates;
DROP TABLE #LoanPeriods;
DROP TABLE #FinalPeriods;