How to calculate the maximum cumulative value for three consecutive hours

-- How to calculate the maximum cumulative value for three consecutive hours:
Go
Create Table [DBO]. [tb_rundata] (
[ID] [int] identity (1, 1) not null,
[Stcd] [varchar] (20) Collate chinese_prc_ci_as not null,
[Tm] [datetime] not null,
[Z] [decimal] (9, 3) not null,
Constraint [pk_tb_rundata] primary key clustered
(
[Stcd] ASC,
[Tm] ASC
) With (pad_index = OFF, ignore_dup_key = OFF) on [primary]
) On [primary]

Set identity_insert [DBO]. [tb_rundata] On

Insert tb_rundata ([ID], [stcd], [Tm], [Z]) values (7, 'st101 ', '2017-10-24 00:00:00', 2009 );
Insert tb_rundata ([ID], [stcd], [Tm], [Z]) values (8, 'st101 ', '2017-10-24 01:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (9, 'st101 ', '2017-10-24 02:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (10, 'st101 ', '2017-10-24 03:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (11, 'st101 ', '2017-10-24 04:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (12, 'st101 ', '2017-10-24 05:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (13, 'st101 ', '2017-10-24 06:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (14, 'st101 ', '2017-10-24 07:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (15, 'st101 ', '2017-10-24 08:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (16, 'st101 ', '2017-10-24 09:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (17, 'st101 ', '2017-10-24 10:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (18, 'st101 ', '2017-10-24 11:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (19, 'st101 ', '2017-10-24 12:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (1, 'st101 ', '2017-10-24 13:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (5, 'st101 ', '2017-10-24 14:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (3, 'st101 ', '2017-10-24 15:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (20, 'st101 ', '2017-10-24 16:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (21, 'st101 ', '2017-10-24 17:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (22, 'st101 ', '2017-10-24 18:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (23, 'st101 ', '2017-10-24 19:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (24, 'st101 ', '2017-10-24 20:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (25, 'st101 ', '2017-10-24 21:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (26, 'st101 ', '2017-10-24 22:00:00', 2009 );
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values (27, 'st101 ', '2017-10-24 23:00:00', 2009 );

Select row_number () over (order by TM asc) as num, * from [dbo]. [TB_RUNDATA]

Select * from [dbo]. [TB_RUNDATA]

Drop table TB_RUNDATA

---- Calculate the record with the maximum cumulative value for three consecutive hours
Declare @ mintime int
Select @ mintime = min (datepart (hh, TM) from [TB_RUNDATA]
Print @ mintime

-- Method 1:

; With T
As
(
Select *, num = row_number () over (partition by [STCD] order by [TM])
From [tb_rundata]
),
M
(
Select .*
From t a join t B on A. stcd = B. stcd and B. num = A. Num + 1 and datediff (HH, A. tm, B. tm) = 1
Union
Select .*
From t a join t B on A. stcd = B. stcd and A. num = B. Num + 1 and datediff (HH, B. tm, A. tm) = 1
),
N
(
Select a. stcd, A. Num, A. Z + B. Z + C. z
From m a join m B on A. stcd = B. stcd and A. Num + 1 = B. Num
Join m c on A. stcd = C. stcd and A. Num + 2 = C. Num
)

Select .*
From m a join n B on A. stcd = B. stcd and A. Num between B. Num and B. Num + 2
Where not exists (select 1 from N where STCD = B. STCD and Z> B. Z)

-- Method 2:

SELECT * FROM TB_RUNDATA
Inner join (
SELECT * from
(
SELECT *, (select sum (z) FROM tb_rundata B WHERE B. Tm BETWEEN a. tm
AND dateadd (hh, 2, a. tm) sumb
From TB_RUNDATA
) B
Where not exists (
SELECT *
From TB_RUNDATA a WHERE B. sumb <(select sum (z) FROM tb_rundata B
WHERE B. Tm BETWEEN a. tm AND dateadd (hh, 2, a. tm ))
) B ON a. Tm BETWEEN B. tm AND dateadd (hh, 2, B. tm)

/*
Id stcd tm z num
5 ST101 14:00:00. 000 31.000 15
3 ST101 15:00:00. 000 30.000 16
1 ST101 13:00:00. 000 11.000 14

*/

A self-written Method

If OBJECT_ID ('test') is not null
Drop table test
Go
Create table test (
Dates date,
Value int
)
Go
Insert test
Select '1970-01-01 ', 2 union all
Select '1970-01-02 ', 3 union all
Select '1970-01-03 ', 2 union all
Select '2014-01-04 ', 7 union all
Select '1970-01-05 ', 6 union all
Select '1970-01-07 ', 3 union all
Select '1970-01-09 ', 5 union all
Select '1970-01-10 ', 6 union all
Select '1970-01-11 ', 8 union all
Select '1970-01-13 ', 4 union all
Select '2014-01-14 ', 9 union all
Select '1970-01-15 ', 4 union all
Select '1970-01-16 ', 1 union all
Select '2014-01-18 ', 2
Go

; With t
As (
Select
Dateadd (dd,-ROW_NUMBER () over (order by dates asc), dates) as px,
* From test
), M
As (
Select id = ROW_NUMBER () over (partition by px order by dates asc ),
Px, dates, value from t where px in (
Select px from (
Select px,
Min (dates) as startdate, MAX (dates) as enddate
From t a group by px) a where datediff (dd, a. startdate, a. enddate)> = 2)
),
G (
Select px, dates,
(Select SUM (value) from m s
Where s. id between a. id-2 and a. id and a. px = s. px) as totals from m
Where exists (select 1 from m B where a. px = B. px and B. dates = DATEADD (DD,-2, a. dates ))
)
Select top 3 dates, totals from g
Order by totals desc

/*
Dates totals
---------------------------
2012-01-11 19
2012-01-15 17
2012-01-05 15
*/