Conditional Running Total

Hai,

I have a set of data (approx 8 million rows on average and growing) that is broken up into months per account. With each new month that goes by, a calculation is done to determine if the client has paid his account or not. For every month he makes a payment his account is marked with a Zero (0) and every month he doesn't make a payment his account is marked with a One (1).

The goal is to determine a running total to see how many consecutive months the client has missed payments.

-- Sample Data
declare @data as table 

(
 account varchar(6),
 mnth int,
 paid_indicator tinyint
)

insert into @data(account,mnth,paid_indicator)
values ('ABC123',201101,1),
  ('ABC123',201102,0),
  ('ABC123',201103,1),
  ('ABC123',201104,1),
  ('ABC123',201105,0),
  ('ABC123',201106,0),
  ('ABC123',201107,1),
  ('ABC123',201108,1),
  ('ABC123',201109,1),
  ('ABC123',201110,1),
  ('ABC123',201111,1),
  ('ABC123',201112,0),
  ('ABC123',201201,1),
  ('ZZZ555',201105,1),
  ('ZZZ555',201106,1),
  ('ZZZ555',201107,0),
  ('ZZZ555',201108,0),
  ('ZZZ555',201109,0),
  ('ZZZ555',201110,1)
  
  
  
-- Running Total
select d.*, isnull(rt.rt,0) as running_total
from @data d

outer apply 

(
 -- For this account, get the month of the last non-payment.
 select top 1 lnp.mnth
 from @data lnp
 where lnp.account = d.account
 and lnp.mnth < d.mnth
 and lnp.paid_indicator = 0
 order by lnp.mnth desc
) last_non_pmt

outer apply

(
 -- Then, add up the paid_indicator values for months 

--  since the last non-payment.
 select sum(d2.paid_indicator) as rt
 from @data d2
 where d2.mnth between isnull(last_non_pmt.mnth, d2.mnth) and d.mnth
 and d2.paid_indicator = 1
 and d.paid_indicator = 1
 and d2.account = d.account
) rt

order by d.account, d.mnth