sql server - sql cumulative sum with null values under specific conditions -

i need create table cumulative sum for: - sum of value , null gives null if there isn't further in row value

my initial table, want create cumulative sum looks like:

+--------------------+---------------+------+-----+---------+-------+ | first_mob          | r2009         | r2010|r2011| r2012   | r2013 | +--------------------+---------------+------+-----+---------+-------+ | 0                  | 1             | null |null | null    |null   | | 1                  | 3             | 1    | 2   | 3       |3      | | 2                  | 6             | 6    | 3   | null    |null   | | 3                  | 10            | 17   | null| null    |5      | | 4                  | 61            | 23   | null| 4       |null   |  +--------------------+---------------+------+-----+---------+-------+ 

table want obtain looks like

+--------------------+---------------+------+-----+---------+-------+ | first_mob          | r2009         | r2010|r2011| r2012   | r2013 | +--------------------+---------------+------+-----+---------+-------+ | 0                  | 1             | null |null | null    |null   | | 1                  | 4             | 1    | 2   | 3       |3      | | 2                  | 10            | 7    | 5   | 3       |3      | | 3                  | 20            | 24   | null| 3       |8      | | 4                  | 81            | 47   | null| 7       |null   |  +--------------------+---------------+------+-----+---------+-------+ 

my sql code cumulative sum looks like:

if object_id('tempdb..#suma_risk_prestige_nbr') not null  drop table             #suma_risk_prestige_nbr select tp1.first_mob_indef,        sum(tp2.r2007) r2007,    sum(tp2.r2008) r2008,    sum(tp2.r2009) r2009,    sum(tp2.r2010) r2010,    sum(tp2.r2011) r2011,    sum(tp2.r2012) r2012,    sum(tp2.r2013) r2013   #suma_risk_prestige_nbr #risk_prestige_nbr tp1 inner join #risk_prestige_nbr tp2 on tp1.first_mob_indef>=tp2.first_mob_indef group tp1.first_mob_indef,tp1.r2007,tp1.r2008,tp1.r2009,tp1.r2010, tp1.r2011,tp1.r2012,tp1.r2013 order tp1.first_mob_indef,tp1.r2007,tp1.r2008,tp1.r2009,tp1.r2010, tp1.r2011,tp1.r2012,tp1.r2013 

thanks

i see you're using sql server, read link counting rolling total - calculate running total in sql server.

fastest way in 2012 - use sum(...) on (order ...), in 2008 - use cte sequential row_numbers

so, if nulls after largest sum not critical, (it's not fastest way, fast cte have create table sequential number without gaps).

;with cte (   select      t1.first_mob,      sum(t2.[r2009]) [r2009],      sum(t2.[r2010]) [r2010],      sum(t2.[r2011]) [r2011],      sum(t2.[r2012]) [r2012],      sum(t2.[r2013]) [r2013]   table1 t1      left outer join table1 t2 on t2.first_mob <= t1.first_mob   group t1.first_mob ) select     c1.first_mob,     c1.[r2009] [r2009],     c1.[r2010] [r2010],     c1.[r2011] [r2011],     c1.[r2012] [r2012],     c1.[r2013] [r2013] cte c1 

see sql fiddle example

update query bit weird, it's because i've made unpivot not specify column names everywhere. may possible make more effecient, have this:

;with cte1 (   -- unpivoting columns rows write general queries    select        t1.first_mob,        c.name, c.value    table1 t1        cross apply (            select 'r2009', [r2009] union            select 'r2010', [r2010] union            select 'r2011', [r2011] union            select 'r2012', [r2012] union            select 'r2013', [r2013]        ) c(name, value) ), cte2 (   -- counting running total    select       c1.first_mob, c1.name, c1.value, sum(c2.value) total_value    cte1 c1        inner join cte1 c2 on c2.first_mob <= c1.first_mob , c2.name = c1.name    group c1.first_mob, c1.name, c1.value ), cte3 (   -- counting total sums (need later)    select       c1.name, sum(c1.value) value    cte1 c1    group c1.name ), cte4 (   -- removing unnecessary values    select        c2.first_mob,        c2.name,        case when c3.value = c2.total_value , c2.value null null else c2.total_value end value     cte2 c2        inner join cte3 c3 on c3.name = c2.name ) -- pivoting rows columns select     c4.first_mob,     max(case when c4.name = 'r2009' c4.value end) [r2009],     max(case when c4.name = 'r2010' c4.value end) [r2010],     max(case when c4.name = 'r2011' c4.value end) [r2011],     max(case when c4.name = 'r2012' c4.value end) [r2012],     max(case when c4.name = 'r2013' c4.value end) [r2013] cte4 c4 group c4.first_mob 

see sql fiddle example