Categories
Mastering Development

Calculate Number of Consecutive Days Where a Condition Applies Across Two Columns

I have a table similar to below:

+-------------------------+
¦ ID ¦ Date     ¦ Balance ¦
¦----+----------+---------¦
¦ A  ¦ 20200620 ¦ 150     ¦
¦----+----------+---------¦
¦ A  ¦ 20200621 ¦ -130    ¦
¦----+----------+---------¦
¦ A  ¦ 20200621 ¦ -140    ¦
¦----+----------+---------¦
¦ A  ¦ 20200621 ¦ -200    ¦
¦----+----------+---------¦
¦ A  ¦ 20200622 ¦ 200     ¦
¦----+----------+---------¦
¦ A  ¦ 20200622 ¦ 300     ¦
¦----+----------+---------¦
¦ B  ¦ 20200621 ¦ 350     ¦
¦----+----------+---------¦
¦ B  ¦ 20200621 ¦ 400     ¦
¦----+----------+---------¦
¦ B  ¦ 20200621 ¦ -150    ¦
¦----+----------+---------¦
¦ B  ¦ 20200622 ¦ -200    ¦
¦----+----------+---------¦
¦ B  ¦ 20200622 ¦ -300    ¦
¦----+----------+---------¦
¦ B  ¦ 20200623 ¦ -400    ¦
¦----+----------+---------¦
¦ B  ¦ 20200623 ¦ -500    ¦
+-------------------------+

I need to calculate the number of consecutive days where "Balance <0" for reach ID and for each distinct Date (including the date itself in the calculation). Each Id might have several balances in a given date either positive amount or negative. Even if one balance amount in a given day is negative, the query should take that day into account. The output result should be similar to the table blow:

+--------------------------------------------+
¦ ID ¦ Date     ¦ Number_of_Consecutive_Days ¦
¦----+----------+----------------------------¦
¦ A  ¦ 20200620 ¦ Null                       ¦
¦----+----------+----------------------------¦
¦ A  ¦ 20200621 ¦ 1                          ¦
¦----+----------+----------------------------¦
¦ A  ¦ 20200622 ¦ 1                          ¦
¦----+----------+----------------------------¦
¦ B  ¦ 20200621 ¦ Null                       ¦
¦----+----------+----------------------------¦
¦ B  ¦ 20200622 ¦ 2                          ¦
¦----+----------+----------------------------¦
¦ B  ¦ 20200623 ¦ 3                          ¦
+--------------------------------------------+

Could you please suggest me a way to calculate that? It is highly appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *