Unfortunately I don't have a lot of experience with SQL Server.

I have a table with the following structure and sample data:

What I have to do now is the timestamp where the status 1 is as start, the timestamp of status 2 as the end and count the entries with 0 in between. Unfortunately the entries are not consecutive. So I can't just count the ID forwards or backwards. The result should look like this:

I would be very grateful for a suggestion on how to best solve this. I already tried to solve it with the group by function but I had no success yet.

CodePudding user response：

You've tagged SQL Server 2008 - I believe the following is compatible, of course unable to test.

You can use a lateral join (cross apply) to find the corresponding end timestamp that applies to each row.

Then simply aggregate by the end timestamp and count rows where the status is zero.

with e as (
    select t.timestamp tstart, e.timestamp tend
    from t
    cross apply (
        select Min(timestamp) timestamp
        from t t2
        where t2.status=2
        and t2.timestamp > t.timestamp
    )e
    where status=0
)

select Min(tstart) [Start], Max(tend) [end], count(*) CountStatus0
from e
group by tend
order by [start] desc

See example Fiddle