count consecutive number of -1 in a column. count >=14-CodePudding

I'm trying to figure out query to count "-1" that have occurred for more than 14 times. Can anyone help me here. I tried everything from lead, row number, etc but nothing is working out.

The BP is recorded for every minute and I need to figure the id's who's bp_level was "-1" for more than 14min

CodePudding user response：

You may try the following:

Select Distinct B.Person_ID, B.[Consecutive]
From
(
  Select D.person_ID, COUNT(D.bp_level) Over (Partition By D.grp, D.person_ID Order By D.Time_) [Consecutive]
  From
  (
    Select Time_, Person_ID, bp_level,
           DATEADD(Minute, -ROW_NUMBER() Over (Partition By Person_ID Order By Time_), Time_) grp
    From mytable Where bp_level = -1
  ) D
) B
Where B.[Consecutive] >= 14

See a demo from db<>fiddle. Using SQL Server.

DATEADD(Minute, -ROW_NUMBER() Over (Partition By Person_ID Order By Time_), Time_): to define a unique group for consecutive times per person, where (bp_level = -1).

COUNT(D.bp_level) Over (Partition By D.grp, D.person_ID Order By D.Time_): to find the cumulative sum of bp_level over the increasing of time for each group.

Once a none -1 value appeared the group will split into two groups and the counter will reset to 0 for the other group.

NOTE: this solution works only if there are no gaps between the consecutive times, the time is increased by one minute for each row/ person, otherwise, the query will not work but can be modified to cover the gaps.

CodePudding user response：

with data as (
    select *,
        count(case when bp_level = 1 then 1 end) over
            (partition by person_id order by time) as grp
    from T
)
select distinct person_id
from data
where bp_level = -1
group by person_id, grp
having count(*) > 14; /* = or >= ? */

If you want to rely on timestamps rather than a count of rows then you could use the time difference:

...
-- where 1 = 1 /* all rows */
group by person_id, grp
having datediff(minute, min(time), max(time)) > 14;

The accepted answer would have issues with scenarios where there are multiple rows with the same timestamp if there's any potential for that to happen.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=70a8a42ba00191a5067e4b910318bd1e