SQL 如何实现依次核销关系

Question

需求描述：

目前有销售订单对应的回款数据表，是按每个订单每行对应的回款金额格式记录；

有采购订单对应付款数据表，是由对应的销售订单对应的采购订单每行对应的付款金额格式记录；

要求：按销售订单每行为依据，按回款日期将对应的每笔付款进行核销，核销的部分进行计息计算。

表例内容：

--销售订单对应回款数据表
create table sales (
       XSDDcode varchar(40) null  default '',/*销售订单ID*/
       XSDDitemid varchar(40)  null default '',/*销售订单分录ID*/
       FPcode varchar(40)  null default '',/*销售发票ID*/
       HKcode varchar(40)  null default '',/*销售订单编号*/
       HK_NoteDate varchar(20) null  default '',/*收款日期*/
       FP_Value             decimal(20,8)        null default 0,/*销售发票含税金额*/
       HK_Value           decimal(20,8)        null default 0/*本行回款金额*/
       )


insert into sales values('1#','0001','FP01','HK01','2020-02-01',200,100)
insert into sales values('1#','0001','FP02','HK02','2020-02-15',300,60)
insert into sales values('1#','0001','FP02','HK03','2020-02-20',300,50)
insert into sales values('1#','0001','FP01','HK04','2020-02-28',200,100)

--采购订单对应回款数据表


create table purchases (
       XSDDcode varchar(40) null  default '',/*销售订单ID*/
       XSDDitemid varchar(40)  null default '',/*销售订单分录ID*/

       PurOrderID varchar(40)   default '',/*销售订单ID*/
       PurOrderItemID varchar(4)   default '',/*销售订单分录ID*/

       FKCode     varchar(40)   default '',/*销售订单ID*/
       FKRQ      varchar(20)   default '',/*销售订单分录ID*/
       FK_Value          decimal(20,8)        null default 0/*含税单价*/
    )



insert into purchases values('1#','0001','2#','0001','FK01','2020-01-01',50)
insert into purchases values('1#','0001','2#','0001','FK02','2020-01-03',20)
insert into purchases values('1#','0001','2#','0001','FK03','2020-01-04',100)
insert into purchases values('1#','0001','2#','0001','FK04','2020-01-08',30)
insert into purchases values('1#','0001','2#','0001','FK05','2020-01-11',20)
insert into purchases values('1#','0001','2#','0001','FK06','2020-01-15',50)
insert into purchases values('1#','0001','2#','0001','FK07','2020-01-30',60)

要求最终实现效果：

Answer 1

https://www.jb51.net/article/70731.htm