SQL Count Consecutive Rows-CodePudding

I have a SQL table that I need to count the rows with 0 turnover, but the challenge is they resets. I only need the number of consecutive rows since it last generated any turnover.

Source data (it has a lot of different ID, just using 442 and 4500 in this case):

ID         |Date       |  T/O | 
442        |2019-12-31 |    0 |
442        |2020-01-01 |200.00|
442        |2020-01-02 |    0 |
442        |2020-02-06 |    0 |
442        |2020-02-07 |    0 |
442        |2020-02-08 |    0 |
442        |2020-02-09 |150.00|
442        |2020-02-10 |    0 |
442        |2020-02-11 |    0 |
442        |2020-02-15 |    0 |
4500       |2020-01-01 |    0 |

Intended results:

442        | 3 |
4500       | 1 |

I thought of using LAG(), but the number of rows between turnover generated can vary significantly. Sometimes it can be even 30 rows.

CodePudding user response：

SELECT id, COUNT(*) as [result]
FROM SourceData sd1
WHERE t_o=0 
      AND NOT EXISTS (SELECT 1 
                     FROM SourceData sd2 
                     WHERE sd1.id=sd2.id AND t_o != 0 AND sd2.[Date] > sd1.[Date])
GROUP BY id

DEMO

CodePudding user response：

First we can get the last non-zero date for each id.

select id, max(date) as date
from example
where t_o > 0
group by id

This will not show a value for 4500 because it lacks a non-zero value.

Then we can use this to select and group only the values after those dates, or all rows if there was no non-zero date for an id.

with last_to as(
  select id, max(date) as date
  from example
  where t_o > 0
  group by id
)
select example.id, count(example.t_o)
from example
-- Use a left join to get all ids in example,
-- even those missing from last_to.
left join last_to on last_to.id = example.id
-- Account for the lack of a last_to row
-- if the ID has no non-zero values.
where last_to.date is null
   or example.date > last_to.date
group by example.id

Demonstration.