如何基于Table2累计总和匹配Table1的ProcessDate值

数据表定义 #

Table1 #

-- Table 1 Definition
drop table if exists #Table1
create table #Table1
(
    TREATY_COMPANY_CODE varchar(3),
    CURRENCY varchar(3),
    ProcessDate date,
    RowNumber int,
    Payment_Total decimal(20, 2)
)

insert into #Table1
values 
    ('165', 'USD', '2019-12-31', 1, 32929.92),
    ('165', 'USD', '2019-11-14', 2, 2400.0),
    ('165', 'USD', '2019-10-22', 3, 635.0),
    ('165', 'USD', '2019-03-28', 4, -21808.25),
    ('165', 'USD', '2019-02-13', 5, 54906.57)

Table2 #

drop table if exists #Table2
create table #Table2
(
    PolicyNo int null,
    ZeylRankNo int null,
    TreatyCompanyCode nvarchar(3) null,
    CurrencyType nvarchar(3) null,
    DisposedDate datetime null,
    ProvinceNo nvarchar(3) null,
    GWP decimal(20, 5) null,
    Commission_Received decimal(20, 5) null,
    PrKom decimal(20, 5) null
)
insert into #Table2
values
    ('50620211','0','165','USD',43717,'902','146.45','48.81','97.64'),
    ('12789054','0','165','USD',43717,'902','41.11','13.7','27.41'),
    ('12099876','0','165','USD',43717,'701','1312.44','437.44','875'),
    ('12125423','0','165','USD',43717,'701','0','0','0'),
    ('56718901','0','165','USD',43717,'719','1500','499.95','1000.05'),
    ('23456791','0','165','USD',43717,'720','1500','499.95','1000.05'),
    ('21090323','0','165','USD',43702,'720','2000','500','1500'),
    ('21201921','0','165','USD',43698,'719','1500','724.95','775.05'),
    ('45231905','0','165','USD',43698,'720','1500','724.95','775.05'),
    ('45129834','0','165','USD',43675,'719','1500','499.65','1000.35'),
    ('27819123','0','165','USD',43675,'720','8876','2219','6657'),
    ('28917634','0','165','USD',43675,'701','13953','3488.25','10464.75'),
    ('23179001','0','165','USD',43675,'720','2500','500','2000'),
    ('90030602','0','165','USD',43628,'720','1500','724.95','775.05'),
    ('30402213','0','165','USD',43596,'720','1500','725.1','774.9'),
    ('34244590','0','165','USD',43561,'902','262.22','102.27','159.95'),
    ('12893498','0','165','USD',43561,'701','0','0','0'),
    ('12357634','0','165','USD',43561,'720','1500','724.95','775.05'),
    ('19092334','0','165','USD',43561,'902','273.02','106.48','166.54'),
    ('19003023','0','165','USD',43561,'701','1571.76','612.99','958.77'),
    ('19917823','1','165','USD',43548,'720','-11029','-2680.05','-8348.95'),
    ('29912365','0','165','USD',43515,'902','519.4','103.88','415.52'),
    ('76290123','0','165','USD',43515,'701','1980.6','396.12','1584.48'),
    ('90817623','0','165','USD',43507,'720','13536','3289.25','10246.75'),
    ('23158723','0','165','USD',43442,'720','2500','500','2000'),
    ('23878123','0','165','USD',43341,'701','0','0','0'),
    ('23198323','0','165','USD',43341,'902','2994.9','748.73','2246.17'),
    ('14712345','0','165','USD',43302,'720','1500','724.95','775.05')

需求说明 #

按以下规则为Table2的每行分配Table1中的ProcessDate：

1. 先按DisposedDate对Table2排序，累计计算PrKom的总和；
2. 当累计总和超过Table1中RowNumber=1对应的Payment_Total（32929.92）时，将该行及后续符合条件的行标记为2019-12-31；
3. 完成上述分配后，重置累计总和，继续处理Table1中RowNumber=2的Payment_Total（2400.0），重复累计判断并分配对应的ProcessDate（2019-11-14）；
4. 以此类推，处理完Table1中所有按RowNumber排序的Payment_Total及对应ProcessDate。

当前尝试代码 #

SELECT
       KT.*
       ,(
       CASE WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 1)  THEN CAST('2019-12-31' AS date)
       WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 2) THEN CAST('2019-11-14' AS date)
       WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 3) THEN CAST('2019-10-22' AS date)
       WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 4) THEN CAST('2019-03-28' AS date)
       WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 5) THEN CAST('2019-02-13' AS date)
       ELSE CAST('1900-01-01' AS date) END
       ) AS Date
FROM(
SELECT
       K.*
       ,SUM(K.[Pr-Kom]) OVER(ORDER BY K.RN) AS CumulativeTotal
FROM
(SELECT
       *,
       ROW_NUMBER() OVER(ORDER BY DisposedDate) AS RN
FROM Table2
WHERE TreatyCompanyCode='165'
AND CurrencyType='USD'
) AS K
) AS KT

预期结果 #

预期结果为Table2的所有列加上新增的Date列，其中：

• 前若干行的Date为2019-12-31，直到PrKom累计总和超过32929.92；
• 接下来的行Date为2019-11-14，直到重置后的累计总和超过2400.0；
• 后续行依次对应Table1中剩余的ProcessDate，直到所有行完成分配。

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