3

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 #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 13:01:08.000' as datetime), 80)--3
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 13:05:08.000' as datetime), 81)--3
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 13:10:08.000' as datetime), 63)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 13:15:08.000' as datetime), 64)
insert into #temptable( Source, PollTime, Value) values('ABC', cast('2015-01-03 13:20:08.000' as datetime), 65)

I would like to apply a block value to all values greater than 80, and group them so I can count threshold breaches.

So far I have the following t-sql code:

Select      
    *
from
(
    Select 
        *,
        block = case 
                when Value >= 80 then
                    dense_rank() over (partition by Source order by PollTime)  
                    --dense_rank() over (partition by Source, Value order by PollTime)
                when Value < 80 then 0
            end     
    from #temptable
) as rankdata order by PollTime

I get the following returned data:

Source  PollTime                Value   Block 
ABC     2015-01-03 11:41:08.000 66.00   0
ABC     2015-01-03 11:46:08.000 70.00   0
ABC     2015-01-03 11:55:08.000 66.00   0
ABC     2015-01-03 12:00:08.000 70.00   0
ABC     2015-01-03 12:05:08.000 80.00   5
ABC     2015-01-03 12:11:08.000 81.00   6
ABC     2015-01-03 12:14:08.000 84.00   7
ABC     2015-01-03 12:20:08.000 70.00   0
ABC     2015-01-03 12:22:08.000 60.00   0
ABC     2015-01-03 12:26:08.000 62.00   0
ABC     2015-01-03 12:34:08.000 63.00   0
ABC     2015-01-03 12:40:08.000 80.00   12
ABC     2015-01-03 12:46:08.000 82.00   13
ABC     2015-01-03 12:49:08.000 90.00   14
ABC     2015-01-03 12:50:08.000 70.00   0
ABC     2015-01-03 12:54:08.000 80.00   16
ABC     2015-01-03 13:01:08.000 80.00   17
ABC     2015-01-03 13:05:08.000 81.00   18
ABC     2015-01-03 13:10:08.000 63.00   0
ABC     2015-01-03 13:15:08.000 64.00   0
ABC     2015-01-03 13:20:08.000 65.00   0

However what I cannot figure out and what appropriate dataset I would like returned is:

Source  PollTime                Value   Block 
ABC     2015-01-03 11:41:08.000 66.00   0
ABC     2015-01-03 11:46:08.000 70.00   0
ABC     2015-01-03 11:55:08.000 66.00   0
ABC     2015-01-03 12:00:08.000 70.00   0
ABC     2015-01-03 12:05:08.000 80.00   1
ABC     2015-01-03 12:11:08.000 81.00   1
ABC     2015-01-03 12:14:08.000 84.00   1
ABC     2015-01-03 12:20:08.000 70.00   0
ABC     2015-01-03 12:22:08.000 60.00   0
ABC     2015-01-03 12:26:08.000 62.00   0
ABC     2015-01-03 12:34:08.000 63.00   0
ABC     2015-01-03 12:40:08.000 80.00   2
ABC     2015-01-03 12:46:08.000 82.00   2
ABC     2015-01-03 12:49:08.000 90.00   2
ABC     2015-01-03 12:50:08.000 70.00   0
ABC     2015-01-03 12:54:08.000 80.00   3
ABC     2015-01-03 13:01:08.000 80.00   3
ABC     2015-01-03 13:05:08.000 81.00   3
ABC     2015-01-03 13:10:08.000 63.00   0
ABC     2015-01-03 13:15:08.000 64.00   0
ABC     2015-01-03 13:20:08.000 65.00   0

Keep in mind this is just a sample dataset I am using. The values will change and I will also have more than one source in my production environment. (MS SQL Server 2008R2)

Thanks, Pete

3
  • So you want to group each cluster of records where the value is >= 80 and then rank that group? Feb 19, 2015 at 21:08
  • Hi Mark, Yes that is exactly what I would like to do. Feb 19, 2015 at 21:34
  • Why is it DENSE_RANK and not ROW_NUMBER? Is it because there can be multiple readings per (Source, PollTime) and you want to treat them as one reading?
    – Andriy M
    Feb 20, 2015 at 7:23

3 Answers 3

5

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;
11
  • Can you explain the significance by using the maximum value of an integer (2147483647)? Feb 20, 2015 at 3:16
  • It is used to rank the zeroes last, so that the first non zero group can be raked as 1. Please note that this query will likely perform better than the accepted answer. Feb 20, 2015 at 7:45
  • @spaghettidba: I agree that your solution would probably be more efficient, but that max int thing does seem redundant to me too. You've got Value >= 80 all over the place – what is the chance a row with Value >= 80 can get a bb of 0? Seems like safeguarding against something you haven't thought through properly.
    – Andriy M
    Feb 20, 2015 at 8:08
  • @AndriyM the DENSE_RANK function is applied to the whole set, not only the rows passed to the CASE branch the RANK is in. That's why you need to account for the zeroes and rank them as last: you will report the zero groups as 0 anyway, but the rank must be done properly before that. However, if you feel there's a better solution, I'm more than happy to see it and learn a new trick. Feb 20, 2015 at 8:16
  • All right, will give it a try. In the meantime, your max int check actually causes a minor bug. If the first value in a partition is 80 or greater, its block number will be last rather than 1. (Just change the first 66 to e.g. 86 and you'll see.)
    – Andriy M
    Feb 20, 2015 at 8:45
2

The question is for 2008R2 but here's a way to do it in 2012+ versions, using LAG() and window functions (OVER with ROWS ...):

WITH cte AS 
( SELECT
      *,
      blockstart = CASE WHEN Value >= 80
                         AND COALESCE(LAG(Value) OVER (PARTITION BY Source  
                                                       ORDER BY PollTime), 0) < 80
                     THEN 1 ELSE 0
                   END

  FROM
      temptable
)
SELECT
    Source,
    PollTime,
    Value,
    rnk = CASE WHEN Value < 80 
            THEN 0 
            ELSE SUM(blockstart) OVER (PARTITION BY Source 
                                       ORDER BY PollTime
                                       ROWS BETWEEN UNBOUNDED PRECEDING 
                                                AND CURRENT ROW) 
          END 
FROM
    cte
ORDER BY
    Source,
    PollTime ;

Tested at SQLfiddle, where we can see the differences in the execution plans

3
  • Pretty neat! Kudos. The OP should definitely keep it in mind when he migrates to 2012. Feb 20, 2015 at 11:26
  • This is a very nice solution and yes, we will be upgrading to 2012 in the next few months. I will test this model for performance with my larger datasets when we get to that stage! Feb 20, 2015 at 14:41
  • The support, even though limited, for OVER that they introduced to T-SQL in SS 2005 opened quite a realm of new possibilities. Adding support for analytic functions like LAG and extending the support for OVER in SS 2012 at first seemed to me somewhat bleak in comparison. It's solutions like this that can make the improvement look more impressive. I am impressed, thanks for sharing this.
    – Andriy M
    Feb 27, 2015 at 19:30
1

Here's what you want:

 SELECT t.source,
        t.polltime,
        t.value,
        COALESCE(b.idx, 0) AS Block
    FROM #temptable t
    LEFT JOIN 
    (
    SELECT t1.polltime, 
           DENSE_RANK() OVER (ORDER BY COALESCE(MIN(DATEADD(ms,-1,t2.polltime)), MAX(DATEADD(ms,1,t2.polltime)))) AS idx
    FROM #temptable t1
    LEFT JOIN #temptable t2
        ON t1.PollTime < t2.PollTime 
        AND t2.value < 80
    LEFT JOIN #temptable t3
        ON t1.Polltime > t3.Polltime
        AND t3.value < 80
    WHERE t1.value >= 80
    GROUP BY t1.polltime ) b
        ON t.polltime = b.polltime
    ORDER BY t.polltime

The procedure will also handle cases where value = 80 at the beginning or the end of the polltime block.

Edit: added Dateadd(-1/+1 ms) to subquery to handle case where a 1-length 0-block follows an initial 1-block.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.