sql - 在T-SQL中添加缺失的月份

我有这样的数据:

LoanId  PaymentDate PaymentMonth PaymentAmount
  L1     12-01-2008    01             100
  L2     15-02-2008    02             300
  L3     01-04-2008    04             500
  L3     01-10-2008    10             500

我想为每个loanId添加缺少的PaymentMonth，如下所示:

    LoanId  PaymentYear PaymentMonth PaymentAmount
  L1           2008        01             100
  L1           2008        02              0
  L1           2008        03              0
  ..            ..         ..             ..
  L1           2008        12              0
  L2           2008        01              0
  L2           2008        02             300
  L2           2008        03              0
  ..            ..         ..             ..
  L3           2008        01              0
  L3           2008        02              0
  L3           2008        03              0
  L3           2008        04             500
  ..            ..         ..             ..
  L3           2008        10             500
  ..            ..         ..              ..
  L3           2008        12              0

以前是手动进行的，但从2008-20012年至今已获得了超过10万的LoanId

最佳答案

尝试这样做:

use db_test;
go

create table dbo.test1
(
    loanId          varchar(2),
    paymentDate     date,
    paymentMonth    varchar(2),
    paymentAmount   float
);

set dateformat dmy;

insert into dbo.test1
values
    ('L1', '12-01-2008', '01', 100),
    ('L2', '15-02-2008', '02', 300),
    ('L3', '01-04-2008', '04', 500),
    ('L3', '01-10-2008', '10', 500);

set dateformat ymd;

with cte as (
    select cast('2008-01-31' as date) as month_dt, 1 as month_nm, format(1, 'd2') as paymentMonth
    union all
    select eomonth(dateadd(month, 1, month_dt)), month_nm + 1, format(month(month_dt) % 12 + 1, 'd2')
    from cte
    where month_dt < '2012-12-31'
), cte2 as (
    select
        t.loanId,
        x.month_dt,
        x.paymentMonth
    from (
        select distinct loanId from dbo.test1
    ) t
    join cte x
        on 1 = 1
)
select
    a.loanId, year(a.month_dt) as paymentYear, a.paymentMonth, coalesce(b.sm, 0) as paymentAmount
from
    cte2 a
    left join (
        select loanId, eomonth(paymentDate) as paymentDate, paymentMonth, sum(paymentAmount) as sm
        from dbo.test1
        group by loanId, eomonth(paymentDate), paymentMonth
    ) b
        on a.month_dt = b.paymentDate
        and a.loanId = b.loanId
order by
    paymentYear asc,
    loanId asc,
    paymentMonth;