patternMinor

I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

orderrankgroupallgreaterbreachesthanlikewouldget

Problem

I have the following basic dataset in MS SQL Server 2008R2:

```
create table #temptable
(
Source varchar(255),
PollTime datetime,
Value numeric(20,2)
)

insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 11:41:08.000' as datetime), 66)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 11:46:08.000' as datetime), 70)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 11:55:08.000' as datetime), 66)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:00:08.000' as datetime), 70)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:05:08.000' as datetime), 80)--1
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:11:08.000' as datetime), 81)--1
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:14:08.000' as datetime), 84)--1
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:20:08.000' as datetime), 70)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:22:08.000' as datetime), 60)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:26:08.000' as datetime), 62)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:34:08.000' as datetime), 63)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:40:08.000' as datetime), 80)--2
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:46:08.000' as datetime), 82)--2
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:49:08.000' as datetime), 90)--2
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:50:08.000' as datetime), 70)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 12:54:08.000' as datetime), 80)--3
insert into #temptab

Solution

Here's a solution that uses ranking functions only:

SELECT source, 
    polltime, 
    value, 
    block = 
        CASE -- ranks the islands
            WHEN Value >= 80 THEN DENSE_RANK() OVER (PARTITION BY source, CASE bb WHEN 0 THEN 0 ELSE 1 END ORDER BY bb )
            ELSE 0 
        END  
FROM (
    SELECT *,
        bb = -- creates the "islands"
            CASE 
                WHEN Value >= 80 THEN 
                    ROW_NUMBER() OVER(PARTITION BY source  ORDER BY polltime) - 
                    ROW_NUMBER() OVER(PARTITION BY source, CASE WHEN Value >= 80 THEN 1 ELSE 0 END ORDER BY polltime) + 1
                ELSE 0
            END
    FROM #temptable
) AS rankdata
ORDER BY polltime;

Code Snippets

SELECT source, 
    polltime, 
    value, 
    block = 
        CASE -- ranks the islands
            WHEN Value >= 80 THEN DENSE_RANK() OVER (PARTITION BY source, CASE bb WHEN 0 THEN 0 ELSE 1 END ORDER BY bb )
            ELSE 0 
        END  
FROM (
    SELECT *,
        bb = -- creates the "islands"
            CASE 
                WHEN Value >= 80 THEN 
                    ROW_NUMBER() OVER(PARTITION BY source  ORDER BY polltime) - 
                    ROW_NUMBER() OVER(PARTITION BY source, CASE WHEN Value >= 80 THEN 1 ELSE 0 END ORDER BY polltime) + 1
                ELSE 0
            END
    FROM #temptable
) AS rankdata
ORDER BY polltime;

Context

StackExchange Database Administrators Q#93343, answer score: 5

Revisions (0)

No revisions yet.