SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tmp(
	[Date_Time] [datetime2](0) NOT NULL,
	[MonitorID] [nvarchar](50) NOT NULL,
	[Value] [decimal](15, 2) NULL,
	[Status] [nvarchar](50) NULL
) 
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:00:00.0000000' AS DateTime2), N'1', CAST(4.36 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:00:00.0000000' AS DateTime2), N'15', CAST(2.10 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:06:00.0000000' AS DateTime2), N'1', CAST(4.07 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:06:00.0000000' AS DateTime2), N'15', CAST(2.13 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:12:00.0000000' AS DateTime2), N'1', CAST(3.56 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:12:00.0000000' AS DateTime2), N'15', CAST(2.09 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:18:00.0000000' AS DateTime2), N'1', CAST(3.23 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:18:00.0000000' AS DateTime2), N'15', CAST(2.23 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:24:00.0000000' AS DateTime2), N'1', CAST(2.98 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:24:00.0000000' AS DateTime2), N'15', CAST(2.17 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:30:00.0000000' AS DateTime2), N'1', CAST(2.61 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:30:00.0000000' AS DateTime2), N'15', CAST(2.18 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:36:00.0000000' AS DateTime2), N'1', CAST(2.13 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:36:00.0000000' AS DateTime2), N'15', CAST(2.16 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:42:00.0000000' AS DateTime2), N'1', CAST(1.94 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:42:00.0000000' AS DateTime2), N'15', CAST(2.01 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:48:00.0000000' AS DateTime2), N'1', CAST(1.79 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:48:00.0000000' AS DateTime2), N'15', CAST(1.81 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:54:00.0000000' AS DateTime2), N'1', CAST(1.70 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T00:54:00.0000000' AS DateTime2), N'15', CAST(1.87 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:00:00.0000000' AS DateTime2), N'1', CAST(1.60 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:00:00.0000000' AS DateTime2), N'15', CAST(1.98 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:06:00.0000000' AS DateTime2), N'1', CAST(1.64 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:06:00.0000000' AS DateTime2), N'15', CAST(1.97 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:12:00.0000000' AS DateTime2), N'1', CAST(1.76 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:12:00.0000000' AS DateTime2), N'15', CAST(1.90 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:18:00.0000000' AS DateTime2), N'1', CAST(1.91 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:18:00.0000000' AS DateTime2), N'15', CAST(1.99 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:24:00.0000000' AS DateTime2), N'1', CAST(1.93 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:24:00.0000000' AS DateTime2), N'15', CAST(1.93 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:30:00.0000000' AS DateTime2), N'1', CAST(1.85 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:30:00.0000000' AS DateTime2), N'15', CAST(1.86 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:36:00.0000000' AS DateTime2), N'1', CAST(1.75 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:36:00.0000000' AS DateTime2), N'15', CAST(1.96 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:42:00.0000000' AS DateTime2), N'1', CAST(1.69 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:42:00.0000000' AS DateTime2), N'15', CAST(1.97 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:48:00.0000000' AS DateTime2), N'1', CAST(1.71 AS Decimal(15, 2)), N'3')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:48:00.0000000' AS DateTime2), N'15', CAST(1.89 AS Decimal(15, 2)), N'1')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:54:00.0000000' AS DateTime2), N'1', CAST(1.86 AS Decimal(15, 2)), N'2')
INSERT tmp ([Date_Time], [MonitorID], [Value], [Status]) VALUES (CAST(N'2022-06-01T01:54:00.0000000' AS DateTime2), N'15', CAST(2.00 AS Decimal(15, 2)), N'3')
Select *
From tmp


Date_Time
MonitorID
Value
Status


2022-06-01 00:00:00
1
4.36
1

2022-06-01 00:00:00
15
2.10
1

2022-06-01 00:06:00
1
4.07
2

2022-06-01 00:06:00
15
2.13
2

2022-06-01 00:12:00
1
3.56
3

2022-06-01 00:12:00
15
2.09
3

2022-06-01 00:18:00
1
3.23
2

2022-06-01 00:18:00
15
2.23
2

2022-06-01 00:24:00
1
2.98
3

2022-06-01 00:24:00
15
2.17
3

2022-06-01 00:30:00
1
2.61
1

2022-06-01 00:30:00
15
2.18
1

2022-06-01 00:36:00
1
2.13
2

2022-06-01 00:36:00
15
2.16
3

2022-06-01 00:42:00
1
1.94
1

2022-06-01 00:42:00
15
2.01
2

2022-06-01 00:48:00
1
1.79
3

2022-06-01 00:48:00
15
1.81
1

2022-06-01 00:54:00
1
1.70
2

2022-06-01 00:54:00
15
1.87
3

2022-06-01 01:00:00
1
1.60
3

2022-06-01 01:00:00
15
1.98
1

2022-06-01 01:06:00
1
1.64
2

2022-06-01 01:06:00
15
1.97
3

2022-06-01 01:12:00
1
1.76
1

2022-06-01 01:12:00
15
1.90
2

2022-06-01 01:18:00
1
1.91
1

2022-06-01 01:18:00
15
1.99
3

2022-06-01 01:24:00
1
1.93
1

2022-06-01 01:24:00
15
1.93
2

2022-06-01 01:30:00
1
1.85
3

2022-06-01 01:30:00
15
1.86
1

2022-06-01 01:36:00
1
1.75
2

2022-06-01 01:36:00
15
1.96
3

2022-06-01 01:42:00
1
1.69
1

2022-06-01 01:42:00
15
1.97
2

2022-06-01 01:48:00
1
1.71
3

2022-06-01 01:48:00
15
1.89
1

2022-06-01 01:54:00
1
1.86
2

2022-06-01 01:54:00
15
2.00
3


Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
  ,MonitorID
  ,AVG(Value) AverageValue
  ,'?' Status
  FROM tmp
  group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID
  order by dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID


DateTimePerHour
MonitorID
AverageValue
Status


2022-06-01 00:00:00.000
1
2.837000
?

2022-06-01 00:00:00.000
15
2.075000
?

2022-06-01 01:00:00.000
1
1.770000
?

2022-06-01 01:00:00.000
15
1.945000
?


Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
  ,MonitorID,Status,count(*) StatusCount
From tmp
group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status


DateTimePerHour
MonitorID
Status
StatusCount


2022-06-01 00:00:00.000
1
1
3

2022-06-01 00:00:00.000
1
2
4

2022-06-01 00:00:00.000
1
3
3

2022-06-01 00:00:00.000
15
1
3

2022-06-01 00:00:00.000
15
2
3

2022-06-01 00:00:00.000
15
3
4

2022-06-01 01:00:00.000
1
1
4

2022-06-01 01:00:00.000
1
2
3

2022-06-01 01:00:00.000
1
3
3

2022-06-01 01:00:00.000
15
1
3

2022-06-01 01:00:00.000
15
2
3

2022-06-01 01:00:00.000
15
3
4


select DateTimePerHour
,MonitorID
,AverageValue
,Status
from (
Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
  ,MonitorID
  ,Status
  ,AVG(Value) AverageValue
  ,count(*) StatusCount
  ,Row_Number()over(
    PARTITION BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0) 
    ,MonitorID
    order by count(*) desc
  ) Sort
  From tmp
  group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0)
  ,Status
  ,MonitorID
) k
where Sort = 1


DateTimePerHour
MonitorID
AverageValue
Status


2022-06-01 00:00:00.000
1
2.782500
2

2022-06-01 00:00:00.000
15
2.072500
3

2022-06-01 01:00:00.000
1
1.822500
1

2022-06-01 01:00:00.000
15
1.980000
3

Date_Time	MonitorID	Value	Status
2022-06-01 00:00:00	1	4.36	1
2022-06-01 00:00:00	15	2.10	1
2022-06-01 00:06:00	1	4.07	2
2022-06-01 00:06:00	15	2.13	2
2022-06-01 00:12:00	1	3.56	3
2022-06-01 00:12:00	15	2.09	3
2022-06-01 00:18:00	1	3.23	2
2022-06-01 00:18:00	15	2.23	2
2022-06-01 00:24:00	1	2.98	3
2022-06-01 00:24:00	15	2.17	3
2022-06-01 00:30:00	1	2.61	1
2022-06-01 00:30:00	15	2.18	1
2022-06-01 00:36:00	1	2.13	2
2022-06-01 00:36:00	15	2.16	3
2022-06-01 00:42:00	1	1.94	1
2022-06-01 00:42:00	15	2.01	2
2022-06-01 00:48:00	1	1.79	3
2022-06-01 00:48:00	15	1.81	1
2022-06-01 00:54:00	1	1.70	2
2022-06-01 00:54:00	15	1.87	3
2022-06-01 01:00:00	1	1.60	3
2022-06-01 01:00:00	15	1.98	1
2022-06-01 01:06:00	1	1.64	2
2022-06-01 01:06:00	15	1.97	3
2022-06-01 01:12:00	1	1.76	1
2022-06-01 01:12:00	15	1.90	2
2022-06-01 01:18:00	1	1.91	1
2022-06-01 01:18:00	15	1.99	3
2022-06-01 01:24:00	1	1.93	1
2022-06-01 01:24:00	15	1.93	2
2022-06-01 01:30:00	1	1.85	3
2022-06-01 01:30:00	15	1.86	1
2022-06-01 01:36:00	1	1.75	2
2022-06-01 01:36:00	15	1.96	3
2022-06-01 01:42:00	1	1.69	1
2022-06-01 01:42:00	15	1.97	2
2022-06-01 01:48:00	1	1.71	3
2022-06-01 01:48:00	15	1.89	1
2022-06-01 01:54:00	1	1.86	2
2022-06-01 01:54:00	15	2.00	3

DateTimePerHour	MonitorID	AverageValue	Status
2022-06-01 00:00:00.000	1	2.837000	?
2022-06-01 00:00:00.000	15	2.075000	?
2022-06-01 01:00:00.000	1	1.770000	?
2022-06-01 01:00:00.000	15	1.945000	?

DateTimePerHour	MonitorID	AverageValue	Status
2022-06-01 00:00:00.000	1	2.782500	2
2022-06-01 00:00:00.000	15	2.072500	3
2022-06-01 01:00:00.000	1	1.822500	1
2022-06-01 01:00:00.000	15	1.980000	3