Adding Missing Month in T-SQL

Question

I have a data something like this:

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

I want to add missing PaymentMonth's for each loanId's, like this:

    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

Was doing it manually, but now got more than 100k LoanId's from 2008-20012

have a look here: https://stackoverflow.com/questions/7885851/months-between-two-dates you could create a list of all months first and then join with your results. — Esteban P., Aug 28 '17 at 09:43

score 2 · Accepted Answer · answered Aug 28 '17 at 09:52

Try to do this:

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;

Thank you, It worked pretty well Other answers are also working but taking a long time or require some precise edits for the whole dataframe. This one is quite simple to work with — aks2200, Aug 28 '17 at 15:12
If you will have problems with recursive cte, just add this in the end of the query: OPTION (MAXRECURSION 32000). — Evgeny, Aug 28 '17 at 16:54

score 2 · Answer 2 · answered Aug 28 '17 at 09:53

you could try like:

1.) Getting your MIN & MAX PaymentDate (as i asume those are the your ranges)
2.) Creating all months within this range - in my example with a common table expression).
3.) Finally selecting your data and joining with those month-dates and grouping the result

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SET @StartDate = SELECT MIN(PaymentDate) 
                   FROM yourtable

SET @EndDate   = SELECT MAX(PaymentDate) 
                   FROM yourtable

;WITH CTE AS (
    SELECT DATEADD(MONTH, x.number, @StartDate) as Months
      FROM master.dbo.spt_values x
     WHERE x.type = 'P'        
       AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)
)
  SELECT yourtable.LoanID
        ,yourtable.PaymentYear
        ,yourtable.PaymentMonth
        ,SUM(ISNULL(PaymentAmount,0)) as PaymentAmount
    FROM CTE
    INNER JOIN yourtable
      ON yourtable.PaymentYear = CONVERT(VARCHAR(4),DATEPART(YEAR, Months)) 
     AND yourtable.PaymentMonth = RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(MONTH, Months)),2)
GROUP BY yourtable.LoanID
        ,yourtable.PaymentYear
        ,yourtable.PaymentMonth

score 1 · Answer 3 · answered Aug 28 '17 at 10:03

This is one way of doing it, pretty straightforward. Necessary comments are in the code.

declare @LoanData table (
ID char(2),
PaymentDate date,
PaymentAmount int
)
insert into @LoanData values
('L1', '01-12-2008',100),
('L2', '02-15-2008',300),
('L3', '04-01-2008',500),
('L3', '10-01-2008',500)
declare @TableID table(id char(2))
--list of IDs
insert into @TableID select distinct ID from @LoanData

declare @PaymentMonth table(
LoanID char(2),
PaymentYear int,
PaymentMonth int,
PaymentAmount int
)
declare @month int, @year int, @i int, @id char(2)
select @i = count(*) from @TableID

--first get the table which has recotrd for every month for every id (default value in PaymentAmount is 0)
while @i > 0
begin
    select top 1 @id = id from @TableID

    set @year=2008
    while @year <= 2012
    begin
        set @month=1
        while @month <= 12
        begin
            insert into @PaymentMonth values (@id, @year, @month, 0)
            set @month = @month + 1
        end

        set @year = @year + 1
    end
    delete from @TableID where id = @id
    set @i = @i - 1
end
--update table based on your initial data
update @PaymentMonth
set PaymentAmount = A.PaymentAmount from @LoanData as A
where LoanID = A.ID and PaymentYear = datepart(YEAR, A.PaymentDate) and PaymentMonth = datepart(MONTH, A.PaymentDate)

select * from @PaymentMonth

score 0 · Answer 4 · answered Aug 29 '17 at 04:41

create table temp_loantable (
loanid bigint,
paymentdate date,
paymentmonth varchar(2),
paymentamount numeric(10,2))

Having a date range ( year & month) and then doing a left outer join should get the desired output.

select * from 
(select years.n yearval , months.n monthval
from (values(2008), (2009), (2010), (2011), (2012)) years(n),
(values(1),(2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) months(n)) a
left outer join temp_loantable l 
on year(paymentdate) = a.yearval 
   and month(paymentdate) = a.monthval

Adding Missing Month in T-SQL

4 Answers4