Search code examples
sqlsql-servercumulative-sumgaps-and-islands

Gaps and Islands but with a lot of nulls

I have source data like following table and trying to get desired output.

Basically I want to running total until status changes to fail.

date id tg_id status DESIRED_OUTPUT
12/31/2019 123456 0
1/1/2020 123456 0
1/2/2020 123456 0
1/3/2020 123456 752 FAIL 1
1/4/2020 123456 1
1/5/2020 123456 1
1/6/2020 123456 1
1/7/2020 123456 1
1/8/2020 123456 752 FAIL 2
1/9/2020 123456 2
1/10/2020 123456 2

I know this gaps and island problem but the issue I am running into is when the status is not fail and it's nulls I am not sure how to handle.

Solution

  • This is actually just a cumulative windowed sum:

    select *, 
  IsNull(Sum(case when status = 'FAIL' then 1 end) 
           over(partition by id order by date)
  , 0) as Desired_Output
from t;

    Demo Fiddle