identify consecutive records greater than 1

• HeftSteady

  Old Hand

  Points: 345

  More actions

  December 3, 2024 at 5:51 pm

  #4492743

  Hello

  I need help identifying all records that have consecutive hours (time in order) of greater than 1 for the value field.

  The output I am looking for would have Number,Start Time,End Time, Average of val over that time range that is identified.

  create table LunchTable (
  Numberinteger,
  Value decimal(15,2),
  StartTime datetime
  
          );
  
  
  insert into LunchTable values (1,1.56,'3/14/20 0:00');
  insert into LunchTable values (1,1.3,'3/14/20 1:00');
  insert into LunchTable values (1,0.05,'3/14/20 2:00');
  insert into LunchTable values (1,0.4,'3/14/20 3:00');
  insert into LunchTable values (1,0.1,'3/14/20 4:00');
  insert into LunchTable values (1,0.1,'3/14/20 5:00');
  insert into LunchTable values (1,3.1,'3/14/20 6:00');
  insert into LunchTable values (1,4.15,'3/14/20 7:00');
  insert into LunchTable values (1,4.25,'3/14/20 8:00');
  insert into LunchTable values (1,4.45,'3/14/20 9:00');
  insert into LunchTable values (1,3.15,'3/14/20 10:00');
  insert into LunchTable values (1,3.25,'3/14/20 11:00');
  insert into LunchTable values (1,3,'3/14/20 12:00');
  insert into LunchTable values (1,3,'3/14/20 13:00');
  insert into LunchTable values (1,3,'3/14/20 14:00');
  insert into LunchTable values (1,3,'3/14/20 15:00');
  insert into LunchTable values (1,4.5,'3/14/20 16:00');
  insert into LunchTable values (1,5,'3/14/20 17:00');
  insert into LunchTable values (1,5,'3/14/20 18:00');
  insert into LunchTable values (1,5.5,'3/14/20 19:00');
  insert into LunchTable values (1,6,'3/14/20 20:00');
  insert into LunchTable values (1,5.5,'3/14/20 21:00');
  insert into LunchTable values (1,4.5,'3/14/20 22:00');
  insert into LunchTable values (1,3.5,'3/14/20 23:00');
  insert into LunchTable values (2,0.05,'3/14/20 0:00');
  insert into LunchTable values (2,0.1,'3/14/20 1:00');
  insert into LunchTable values (2,0.1,'3/14/20 2:00');
  insert into LunchTable values (2,0.4,'3/14/20 3:00');
  insert into LunchTable values (2,1.3,'3/14/20 4:00');
  insert into LunchTable values (2,1.56,'3/14/20 5:00');
  insert into LunchTable values (2,3,'3/14/20 6:00');
  insert into LunchTable values (2,3,'3/14/20 7:00');
  insert into LunchTable values (2,3,'3/14/20 8:00');
  insert into LunchTable values (2,3,'3/14/20 9:00');
  insert into LunchTable values (2,3.1,'3/14/20 10:00');
  insert into LunchTable values (2,3.15,'3/14/20 11:00');
  insert into LunchTable values (2,3.25,'3/14/20 12:00');
  insert into LunchTable values (2,3.5,'3/14/20 13:00');
  insert into LunchTable values (2,4.15,'3/14/20 14:00');
  insert into LunchTable values (2,4.25,'3/14/20 15:00');
  insert into LunchTable values (2,4.45,'3/14/20 16:00');
  insert into LunchTable values (2,4.5,'3/14/20 17:00');
  insert into LunchTable values (2,4.5,'3/14/20 18:00');
  insert into LunchTable values (2,5,'3/14/20 19:00');
  insert into LunchTable values (2,5,'3/14/20 20:00');
  insert into LunchTable values (2,5.5,'3/14/20 21:00');
  insert into LunchTable values (2,5.5,'3/14/20 22:00');
  insert into LunchTable values (2,6,'3/14/20 23:00');
  insert into LunchTable values (8,2.15,'3/14/20 0:00');
  insert into LunchTable values (8,2.35,'3/14/20 1:00');
  insert into LunchTable values (8,1.52,'3/14/20 2:00');
  insert into LunchTable values (8,1.3,'3/14/20 3:00');
  insert into LunchTable values (8,1.1,'3/14/20 4:00');
  insert into LunchTable values (8,2.35,'3/14/20 5:00');
  insert into LunchTable values (8,4.15,'3/14/20 6:00');
  insert into LunchTable values (8,5,'3/14/20 7:00');
  insert into LunchTable values (8,5.15,'3/14/20 8:00');
  insert into LunchTable values (8,5.5,'3/14/20 9:00');
  insert into LunchTable values (8,4.5,'3/14/20 10:00');
  insert into LunchTable values (8,4,'3/14/20 11:00');
  insert into LunchTable values (8,3.35,'3/14/20 12:00');
  insert into LunchTable values (8,4,'3/14/20 13:00');
  insert into LunchTable values (8,3,'3/14/20 14:00');
  insert into LunchTable values (8,3,'3/14/20 15:00');
  insert into LunchTable values (8,3,'3/14/20  16:00');
  insert into LunchTable values (8,3,'3/14/20  17:00');
  insert into LunchTable values (8,3,'3/14/20  18:00');
  insert into LunchTable values (8,3,'3/14/20  19:00');
  insert into LunchTable values (8,3,'3/14/20  20:00');
  insert into LunchTable values (8,3,'3/14/20  21:00');
  insert into LunchTable values (8,3,'3/14/20  22:00');
  insert into LunchTable values (8,3,'3/14/20  23:00');
  insert into LunchTable values (8,3,'3/15/20 0:00');
  insert into LunchTable values (8,3,'3/15/20 1:00');
  insert into LunchTable values (8,3,'3/15/20 2:00');
  insert into LunchTable values (8,3,'3/15/20 3:00');
  insert into LunchTable values (8,3,'3/15/20 4:00');
  insert into LunchTable values (8,3,'3/15/20 5:00');
  insert into LunchTable values (8,3,'3/15/20 6:00');
  insert into LunchTable values (8,3,'3/15/20 7:00');
  insert into LunchTable values (8,3,'3/15/20 8:00');
  insert into LunchTable values (8,3,'3/15/20 9:00');
  insert into LunchTable values (8,3,'3/15/20 10:00');
  insert into LunchTable values (8,3,'3/15/20 11:00');
  insert into LunchTable values (8,3,'3/15/20 12:00');
  insert into LunchTable values (8,3,'3/15/20 13:00');
  insert into LunchTable values (8,3,'3/15/20 14:00');
  insert into LunchTable values (8,3,'3/15/20 15:00');
  insert into LunchTable values (8,3,'3/15/20  16:00');
  insert into LunchTable values (8,3,'3/15/20  17:00');
  insert into LunchTable values (8,3,'3/15/20  18:00');
  insert into LunchTable values (8,3,'3/15/20  19:00');
  insert into LunchTable values (8,3,'3/15/20  20:00');
  insert into LunchTable values (8,3,'3/15/20  21:00');
  insert into LunchTable values (8,3,'3/15/20  22:00');
  insert into LunchTable values (8,3,'3/15/20  23:00');
  
  
  
  insert into LunchTable values (9,2.15,'3/14/20 0:00');
  insert into LunchTable values (9,2.35,'3/14/20 1:00');
  insert into LunchTable values (9,1.52,'3/14/20 2:00');
  insert into LunchTable values (9,1.3,'3/14/20 3:00');
  insert into LunchTable values (9,1.1,'3/14/20 4:00');
  insert into LunchTable values (9,2.35,'3/14/20 5:00');
  insert into LunchTable values (9,4.15,'3/14/20 6:00');
  insert into LunchTable values (9,5,'3/14/20 7:00');
  insert into LunchTable values (9,5.15,'3/14/20 8:00');
  insert into LunchTable values (9,5.5,'3/14/20 9:00');
  insert into LunchTable values (9,4.5,'3/14/20 10:00');
  insert into LunchTable values (9,4,'3/14/20 11:00');
  insert into LunchTable values (9,3.35,'3/14/20 12:00');
  insert into LunchTable values (9,4,'3/14/20 13:00');
  insert into LunchTable values (9,3,'3/14/20 14:00');
  insert into LunchTable values (9,3,'3/14/20 15:00');
  insert into LunchTable values (9,3,'3/14/20  16:00');
  insert into LunchTable values (9,3,'3/14/20  17:00');
  insert into LunchTable values (9,3,'3/14/20  18:00');
  insert into LunchTable values (9,3,'3/14/20  19:00');
  insert into LunchTable values (9,3,'3/14/20  20:00');
  insert into LunchTable values (9,3,'3/14/20  21:00');
  insert into LunchTable values (9,3,'3/14/20  22:00');
  insert into LunchTable values (9,3,'3/14/20  23:00');
  insert into LunchTable values (9,3,'3/15/20 0:00');
  insert into LunchTable values (9,3,'3/15/20 1:00');
  
  
  
  select * from LunchTable;

   

   

• DesNorton

  SSC-Insane

  Points: 24392

  More actions

  December 4, 2024 at 6:01 am

  #4493011

  You dont show your expected output.

  Maybe this will point you in the right direction

  WITH cteLag AS (
      SELECT  lt.Number
            , lt.Value
            , lt.StartTime
            , prevVal = LAG(lt.Value) OVER (PARTITION BY lt.Number ORDER BY lt.StartTime)
            , nxtVal  = LEAD(lt.Value) OVER (PARTITION BY lt.Number ORDER BY lt.StartTime)
      FROM    LunchTable AS lt
  )
  SELECT cte.Number
       , cte.Value
       , cte.StartTime
  FROM cteLag AS cte
  WHERE cte.Value > 1
    AND (cte.prevVal > 1 OR cte.nxtVal > 1)
  ORDER BY cte.Number, cte.StartTime;
  

  ---

  How to post data/code on a forum to get the best help.