Enter missing month and calculate running total in one stored procedure

Question

How can I select data from this table

Yr     Month    El1       Value    
----   ------   -------   ------

2017   2        AT010     100        
2017   3        AT010     100         
2017   4        AT010     50        
2017   5        AT010     150     
2017   3        BE020     10      
.......

and insert it into another table in the following way

Yr     Month    El1       Value    
----   ------   -------   ------

2017   0        AT010     0        
2017   1        AT010     0         
2017   2        AT010     100        
2017   3        AT010     200  
2017   4        AT010     250         
2017   5        AT010     400        
2017   6        AT010     400        
2017   7        AT010     400        
2017   8        AT010     400    
2017   9        AT010     400  
2017   10        AT010     400    
2017   11        AT010     400        
2017   12        AT010     400    
2017   0        BE020     0  
2017   1        BE020     0    
2017   2        BE020     0  
2017   3        BE020     10   
2017   4        BE020     10   
2017   5        BE020     10   
2017   6        BE020     10   
2017   7        BE020     10   
2017   8        BE020     10    
2017   9        BE020     10   
2017   10        BE020    10   
2017   11        BE020    10    
2017   12        BE020    10 

.......

I am trying to insert missing months from 0 to 12 and calculate running total at the same time. I used this suggestion for running total calculation; however, I can't figure out how to enter missing month. This code will be used in the stored procedure for a daily ETL job.

Month 0 is balance at the end of previous year – ogull Sep 05 '17 at 13:00 — ogull, Sep 05 '17 at 13:00

score 2 · Accepted Answer · answered Sep 05 '17 at 12:29

2

Hmmm . . . Generate the rows and then use left join or outer apply to bring in the values. Here is one way:

with yyyymm as (
      select 2017 as yr, 1 as mom
      union all
      select yr, mon + 1
      from yyyymm
      where mon + 1 <= 12
     )
select yyyymm.yr, yyyymm.mon, coalesce(e.el1, 0) as el1
from yyyymm cross join
     (select distinct el1 from t) e outer apply
     (select sum(t.value)
      from t
      where t.el1 = e.el1 and
            t.yr = yyyy.yr and
            t.month <= yyyy.mon
     ) tt
order by e.el1, yyyy.yr, yyyy.month;

answered Sep 05 '17 at 12:29

Gordon Linoff

1,242,037
58
646
786

1

i always love Gordons solutions! – Esteban P. Sep 05 '17 at 12:53
Thanks for quick reply Gordon, your solution worked for me. – ogull Sep 05 '17 at 13:50

Enter missing month and calculate running total in one stored procedure

1 Answers1