SQL Server : grouping data for year with CTE

Question

Example data :

create table #temp
(
    dateRef date,
    a float,
    b float,
    c float  
)

insert into #temp (dateRef, a, b, c) values ('20050101',1000,500,0)
insert into #temp (dateRef, a, b, c) values ('20050201',1000,1000,0)
insert into #temp (dateRef, a, b, c) values ('20050301',4000,4000,3000)
insert into #temp (dateRef, a, b, c) values ('20050401',2000,2000,1000)
insert into #temp (dateRef, a, b, c) values ('20050501',1000,2000,0)
insert into #temp (dateRef, a, b, c) values ('20050601',2000,2000,0)
insert into #temp (dateRef, a, b, c) values ('20050701',2000,2000,1000)
insert into #temp (dateRef, a, b, c) values ('20050801',2000,2000,1000)
--- 
insert into #temp (dateRef, a, b, c) values ('20060301',1000,1000,0)
insert into #temp (dateRef, a, b, c) values ('20060601',1000,1000,0)
insert into #temp (dateRef, a, b, c) values ('20060701',2000,2000,0)
insert into #temp (dateRef, a, b, c) values ('20060801',2000,2000,1000)
---
insert into #temp (dateRef, a, b, c) values ('20070101',1000,1000,0)

What is best way to have this expected result? (using SQL Server 2012)

DateRef         a       b           c
----------------------------------------    
--- 20050301    6000    5500        3000    
--- 20050401    2000    2000        1000
--- 20050701    5000    6000        1000    
--- 20050801    2000    2000        1000    
--- 20060801    6000    6000        1000    

Logic: to sum all previous A for Date consecutive and Year breack when C>0 , example : when data='20050301', c=3000 (c>0) then i need sum(a) if date is consecutive( in this case they are , 20050101-20050201-20050301), when data=20050701 c=1000 (c>0) than sum(a) if date is consecutive( in this case they are , 20050501-20050601-20050701), when date='20050801' c=1000 (c>0) then i need sum(a) in this case only year 20050801 and so on

Answer 1

Try this query:

select
    dateRef = max(dateRef), a = sum(a), b = sum(b), c = sum(c)
from (
    select 
        *, rn = datediff(mm, '19000101', dateRef) - row_number() over (order by dateRef)
        , grp = isnull(sum(iif(c > 0, 1, 0)) over (order by dateRef rows between unbounded preceding and 1 preceding), 0)
    from 
        #temp
) t
group by rn, grp
having sum(c) > 0

Two columns are used for grouping in the query. rn - looks for consecutive rows, grp - groups rows where c > 0 with previous rows where c = 0.

Output

dateRef     a       b       c
---------------------------------
2005-03-01  6000    5500    3000
2005-04-01  2000    2000    1000
2005-07-01  5000    6000    1000
2005-08-01  2000    2000    1000
2006-08-01  5000    5000    1000

Answer 2

For Grouping data by year, we can try this-

select
    year(dateref) as year,
    sum(a) as a,
    sum(b) as b,
    sum(c) as c
from #temp
group by year(dateref)

Output -

year    a       b       c
2005    15000   15500   6000
2006    6000    6000    1000
2007    1000    1000    0