with mid_records as (
-- 加入row_number()，按prj_id分组，按raw_records.date 正序排序
    select records.*,
    row_number() over(partition by prj_id order by raw_records.date asc) prj_r  -- spoil alert: 出问题的地方
    from raw_records
), records as (
-- 加入last_date
    select mid_records.*, cast(null as datetime2) last_date
    from mid_records
    where mid_records.prj_r = 1

union all

    select
    mid_records.*,
    case when datepart(year, last_record.date) = datepart(year, raw_records.date) and datepart(month, last_record.date) = datepart(month, raw_records.date) then last_record.last_date  -- 如果上一条是同一个月的，用上一条的last_date
    else last_record.date end last_date
    from records last_record
    join mid_records
    on last_record.prj_id = raw_records.prj_id
    AND last_record.prj_r + 1 = raw_records.prj_r
)

select
records.*,
mat_summary.sum_value

from records

outter apply(
select
sum(value)
from mat
where mat.prj_id = records.prj_id
and mat.date < last_second_of_month(records.date)  -- 取records.date当月以及之前未重复关联的mat进行汇总
and (records.last_date is null or mat.date > first_date_of_month(records.last_date))
) mat_summary