TSQL to get running total according to work days

Question

I want the total payment amount for all workdays plus the running total of payment amount from non-workdays. For example, I have the below table with payment amounts per day of the month.

create table #payment_amounts
(
ReceivedDate date
,PaymentAmount decimal(13,2)
,PostingDay int
)

insert #payment_amounts values('2/1/15',100000.00,0)
insert #payment_amounts values('2/2/15',200000.00,1)
insert #payment_amounts values('2/3/15',300000.00,1)
insert #payment_amounts values('2/4/15',400000.00,1)
insert #payment_amounts values('2/5/15',500000.00,1)
insert #payment_amounts values('2/6/15',100000.00,1)
insert #payment_amounts values('2/7/15',200000.00,1)
insert #payment_amounts values('2/8/15',300000.00,0)
insert #payment_amounts values('2/9/15',400000.00,0)
insert #payment_amounts values('2/10/15',500000.00,1)

The output for this looks like:

ReceivedDate    PaymentAmount   PostingDay
2015-02-01  100000.00   0
2015-02-02  200000.00   1
2015-02-03  300000.00   1
2015-02-04  400000.00   1
2015-02-05  500000.00   1
2015-02-06  100000.00   1
2015-02-07  200000.00   1
2015-02-08  300000.00   0
2015-02-09  400000.00   0
2015-02-10  500000.00   1

So what I want is the PaymentAmount value for only rows that have PostingDay = 1. But if the prior dates are PostingDay = 0, then I want the sum of those PaymentAmounts. So from the above data, I want it to look like below:

ReceivedDate    PaymentAmount   PostingDay  RunningTotal
2/1/2015     100,000.00      -       -   
2/2/2015     200,000.00      1   300,000.00 
2/3/2015     300,000.00      1   300,000.00 
2/4/2015     400,000.00      1   400,000.00 
2/5/2015     500,000.00      1   500,000.00 
2/6/2015     100,000.00      1   100,000.00 
2/7/2015     200,000.00      1   200,000.00 
2/8/2015     300,000.00      -       -   
2/9/2015     400,000.00      -       -   
2/10/2015    500,000.00      1   1,200,000.00 

How can I do this?

Answer 1

Bit of a head-scratcher, but give this a try:

;with cte1 as (
    Select A.*
          ,LagGrp = Lag(Grp,1) over (Order by ReceivedDate)
     From (
            Select *
                  ,Grp = IIF(PostingDay=1,null,Row_Number() over (Order By ReceivedDate) - Row_Number() over (Partition By PostingDay Order By ReceivedDate))
            From  #payment_amounts
          ) A
)
Select A.ReceivedDate
      ,A.PaymentAmount
      ,A.PostingDay  
      ,RunningTotal  = (PaymentAmount + IsNull((Select sum(PaymentAmount) From cte1 Where Grp=A.LagGrp),0)) * PostingDay
 from  cte1 A      
 Order By 1

Returns