Running Total with Reset for 1 after a 0


  

/*
create table test (id1 int, id2 int,month int,YEAR int,  Occurrence int)
 insert into test values (500,7,1,2015,1),(500,7,2,2015,1),(500,7,3,2015,1)
,(500,7,4,2015,1),(500,7,5,2015,1),(500,7,6,2015,1)
,(500,7,7,2015,1),(500,7,8,2015,0),(500,7,9,2015,1)
,(500,7,10,2015,0),(500,7,11,2015,0)

*/
create table test (id1 int, id2 int,month int,YEAR int,  Occurrence int)

 insert into test values(123,5,1,2011,1),(123,5,2,2011,1)
 ,(123,5,3,2011,1),(123,5,4,2011,1),(123,5,5,2011,0)
 ,(123,5,6,2011,0),(123,5,7,2011,0),(123,5,8,2011,0)
 ,(123,5,9,2011,0),(123,5,10,2011,0),(123,5,11,2011,0)
 ,(232,4,5,2011,0),(232,4,6,2011,0),(232,4,7,2011,1)
 ,(232,4,8,2011,1),(458,2,2,2011,0),(458,2,3,2011,0)
 ,(458,2,4,2011,0),(458,2,5,2011,1),(458,2,6,2011,1)
 ,(458,2,7,2011,1),(458,2,8,2011,0),(458,2,9,2011,0)
 ,(458,2,10,2011,1),(458,2,11,2011,1),(569,1,1,2010,0)
 ,(569,1,2,2010,0),(569,1,3,2010,0),(569,1,4,2010,1)
 ,(569,1,5,2010,1),(569,1,6,2010,1),(569,1,7,2010,1)
 ,(569,1,8,2010,1),(569,1,9,2010,1),(569,1,10,2010,1)
 ,(569,1,11,2010,0),(569,1,12,2010,0),(569,1,1,2011,0)
 ,(569,1,2,2011,0),(569,1,3,2011,1),(569,1,4,2011,1)
 ,(569,1,5,2011,1),(569,1,6,2011,1)

;with mycte as (
select id1,id2, year, month, occurrence
,row_number()Over( Partition by id1 Order by year, month) rn
from test
)

,mycte1 as
(select id1, id2, year, month, rn,occurrence, occurrence as expectedOutput from mycte
 where rn=1 

union all
select m1.id1, m1.id2, m.year,m.month, m1.rn+1 rn
 ,m.occurrence, case when  m.occurrence=0 then m1.expectedOutput else m.occurrence+m1.expectedOutput*m1.occurrence end
from mycte1 m1 join mycte m on m.rn=m1.rn+1 and m.id1=m1.id1)

Select id1, id2, year, month, occurrence,  expectedOutput from mycte1
order by id1,rn
option (maxrecursion 0)

Drop table test



---Another solution

;with mycte as
(

select id1,id2,month, year,Occurrence,
row_number()Over(Partition by id1 Order by year, month) - row_number()Over(Partition by id1,Occurrence Order by year, month) grp
from test

)
,mycte1 as (
Select id1,id2,month, year,Occurrence ,grp
, Nullif(Sum(Occurrence) Over(Partition by ID1,grp Order by Year, Month),0) expectedOccurrence
from mycte

)

select id1, id2, month, year, Occurrence,
ISNULL(CAST(SUBSTRING(MAX(CAST(year AS BINARY(4)) + CAST(month AS BINARY(4)) + CAST(expectedOccurrence AS BINARY(4)) )
OVER(Partition by ID1 ORDER BY year,month ROWS UNBOUNDED PRECEDING ),9,4) AS int ) ,0) as expectedOccurrence

from mycte1
order by id1,year, month

 

https://social.msdn.microsoft.com/Forums/en-US/2a28042a-e886-420d-bda3-3d0e5c14859c/count-occurances-without-a-break-using-sql?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s