identify consecutive records greater than 1

Question

Post reply

identify consecutive records greater than 1

HeftSteady

Old Hand

Points: 345

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;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24392 More actions · Answer 1

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.

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 2

Need to know the results, but the lag/lead approach is what I'd do, as @desnorton has shown