-2

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Poojan Patel
  • 19
  • 5

1 Answers1

2

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

Stu
  • 30,392
  • 6
  • 14
  • 33
  • this will give sum but then where there are gaps it should give 0. Example from 1/3 till 1/7 it should carry that 1. Right now it will only give 1 for 1/3 and rest is 0 – Poojan Patel Jul 16 '23 at 19:02
  • 1
    @PoojanPatel The [Demo fiddle](https://dbfiddle.uk/UcWMnFZz) matches the desired results in your question - have you viewed it? – Stu Jul 16 '23 at 19:05
  • Hi @stu , yes I saw it however I think for same ID if tg_id also changes then might need to start another transaction. Meaning for 752 it's fine I was thinking maybe need to include this in partition by as well. – Poojan Patel Jul 16 '23 at 19:35
  • 2
    Ok well that's not documented at all in your question there is no mention of this column, but that's probably very easy for you to test? – Stu Jul 16 '23 at 19:45