Query consecutive values, starting, ending, min and max above certain treshold

• sibir1us

  SSC Eights!

  Points: 828

  More actions

  September 20, 2009 at 1:06 am

  #134007

  I have a table t1 with columns like this: ID int, value int.

  ID value

  ----------- -----------

  1 1

  2 1

  3 2

  4 5

  5 5

  6 5

  7 3

  8 1

  9 0

  10 11

  11 24

  12 17

  13 2

  14 1

  15 1

  16 23

  17 15

  18 17

  19 0

  20 1

  The challenge is to present a result set which is based on several parameters. Show the startingID and the EndingID of all consecutive records with value greater than 10 and with more than 2 consecutive occurrences. Also, show the first value of the set, the last value, and the min and max value of the current consecutive occurrence group.

  The above query based on the data in t1 would return the following:

  StartingID EndingID StartingValue EndingValue MinValue MaxValue

  10 12 11 17 11 24

  16 18 23 17 15 23

  I hope this challenge can be solved in sql server 2005/2008.

  Make everything as simple as possible, but not simpler.
  Albert Einstein

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  September 20, 2009 at 2:20 am

  #1056028

  SET NOCOUNT ON

  DECLARE @T1 TABLE(ID INT, value INT)

  INSERT INTO @T1(ID,value)

  SELECT 1, 1 UNION ALL

  SELECT 2, 1 UNION ALL

  SELECT 3, 2 UNION ALL

  SELECT 4, 5 UNION ALL

  SELECT 5, 5 UNION ALL

  SELECT 6, 5 UNION ALL

  SELECT 7, 3 UNION ALL

  SELECT 8, 1 UNION ALL

  SELECT 9, 0 UNION ALL

  SELECT 10, 11 UNION ALL

  SELECT 11, 24 UNION ALL

  SELECT 12, 17 UNION ALL

  SELECT 13, 2 UNION ALL

  SELECT 14, 1 UNION ALL

  SELECT 15, 1 UNION ALL

  SELECT 16, 23 UNION ALL

  SELECT 17, 15 UNION ALL

  SELECT 18, 17 UNION ALL

  SELECT 19, 0 UNION ALL

  SELECT 20, 1;

  WITH CTE1 AS (

  SELECT ID,value,

  ID-ROW_NUMBER() OVER(PARTITION BY CASE WHEN value>10 THEN 1 ELSE 0 END ORDER BY ID) AS Grp

  FROM @T1),

  CTE2 AS (

  SELECT ID,value,Grp,

  ROW_NUMBER() OVER(PARTITION BY Grp ORDER BY ID) AS rn,

  ROW_NUMBER() OVER(PARTITION BY Grp ORDER BY ID DESC) AS rnRev

  FROM CTE1

  WHERE value>10)

  SELECT MIN(ID) AS StartingID,

  MAX(ID) AS EndingID,

  MAX(CASE WHEN rn=1 THEN value END) AS StartingValue,

  MAX(CASE WHEN rnRev=1 THEN value END) AS EndingValue,

  MIN(value) AS MinValue,

  MAX(value) AS MaxValue

  FROM CTE2

  GROUP BY grp

  HAVING COUNT(*)>2

  ORDER BY MIN(ID)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537