December 3, 2024 at 5:51 pm
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;
December 4, 2024 at 6:01 am
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;
December 4, 2024 at 6:31 pm
Need to know the results, but the lag/lead approach is what I'd do, as @desnorton has shown
December 6, 2024 at 5:21 pm
Here is a slightly different approach. There is no discernable difference in the performance for the sample data. When I add a primary key on Number and StartTime, this approach is very slighty favored (48% to 52%).
WITH cteSum AS (
SELECT lt.Number
, lt.[Value]
, lt.StartTime
, SUM(CASE WHEN lt.[Value] > 1 THEN 1 ELSE 0 END) OVER(PARTITION BY lt.Number ORDER BY lt.StartTime ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS OverCount
FROM #LunchTable AS lt
)
SELECT cte.Number
, cte.Value
, cte.StartTime
FROM cteSum AS cte
WHERE cte.[Value] > 1
AND cte.OverCount > 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 6, 2024 at 11:48 pm
The queries so far don't identify the ranges of consecutive "greater than one" rows. This query uses two ROW_NUMBER functions which both count up across PARTITION BY Number. To reset the 2nd ROW_NUMBER when the boundry condition changes the function includes the 'gt_one' column in the PARTITION BY. When the 'gt_one' condition changes then the 2nd ROW_COUNTER resets and when the 2nd counter is subtracted from the 1st counter it creates the row groupings 'rn_grp' along with 'gt_one' being 0 or 1
with rn_cte as (
select l.*, v.gt_one,
row_number() over (partition by Number order by StartTime) as rn,
row_number() over (partition by Number, v.gt_one order by StartTime) rn_gt_one,
row_number() over (partition by Number order by StartTime)
- row_number() over (partition by Number, v.gt_one order by StartTime) as rn_grp
from #LunchTable l
cross apply (values (iif(l.[Value]>1, 1, 0))) v(gt_one))
select *
from rn_cte
order by Number, StartTime;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 7, 2024 at 3:01 am
with rn_cte as (
select l.*, v.gt_one,
row_number() over (partition by Number order by StartTime)
- row_number() over (partition by Number, v.gt_one order by StartTime) as rn_grp
from #LunchTable l
cross apply (values (iif(l.[Value]>1, 1, 0))) v(gt_one))
select Number, gt_one, rn_grp, min(StartTime) min_StartTime, max(StartTime) max_StartTime, avg([Value]) avg_val
from rn_cte
group by Number, gt_one, rn_grp
order by Number, rn_grp;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply