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,
	[Status1] [nvarchar](50) NULL,
	[Status2] [nvarchar](50) NULL
)

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:00:00.0000000' AS DateTime2), N'1', CAST(4.36 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:00:00.0000000' AS DateTime2), N'15', CAST(2.10 AS Decimal(15, 2)), N'B', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:06:00.0000000' AS DateTime2), N'1', CAST(4.07 AS Decimal(15, 2)), N'B', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:06:00.0000000' AS DateTime2), N'15', CAST(2.13 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:12:00.0000000' AS DateTime2), N'1', CAST(3.56 AS Decimal(15, 2)), N'C', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:12:00.0000000' AS DateTime2), N'15', CAST(2.09 AS Decimal(15, 2)), N'C', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:18:00.0000000' AS DateTime2), N'1', CAST(3.23 AS Decimal(15, 2)), N'C', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:18:00.0000000' AS DateTime2), N'15', CAST(2.23 AS Decimal(15, 2)), N'C', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:24:00.0000000' AS DateTime2), N'1', CAST(2.98 AS Decimal(15, 2)), N'A', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:24:00.0000000' AS DateTime2), N'15', CAST(2.17 AS Decimal(15, 2)), N'B', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:30:00.0000000' AS DateTime2), N'1', CAST(2.61 AS Decimal(15, 2)), N'A', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:30:00.0000000' AS DateTime2), N'15', CAST(2.18 AS Decimal(15, 2)), N'C', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:36:00.0000000' AS DateTime2), N'1', CAST(2.13 AS Decimal(15, 2)), N'C', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:36:00.0000000' AS DateTime2), N'15', CAST(2.16 AS Decimal(15, 2)), N'B', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:42:00.0000000' AS DateTime2), N'1', CAST(1.94 AS Decimal(15, 2)), N'B', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:42:00.0000000' AS DateTime2), N'15', CAST(2.01 AS Decimal(15, 2)), N'A', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:48:00.0000000' AS DateTime2), N'1', CAST(1.79 AS Decimal(15, 2)), N'B', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:48:00.0000000' AS DateTime2), N'15', CAST(1.81 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:54:00.0000000' AS DateTime2), N'1', CAST(1.70 AS Decimal(15, 2)), N'C', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T00:54:00.0000000' AS DateTime2), N'15', CAST(1.87 AS Decimal(15, 2)), N'C', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:00:00.0000000' AS DateTime2), N'1', CAST(1.60 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:00:00.0000000' AS DateTime2), N'15', CAST(1.98 AS Decimal(15, 2)), N'A', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:06:00.0000000' AS DateTime2), N'1', CAST(1.64 AS Decimal(15, 2)), N'B', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:06:00.0000000' AS DateTime2), N'15', CAST(1.97 AS Decimal(15, 2)), N'B', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:12:00.0000000' AS DateTime2), N'1', CAST(1.76 AS Decimal(15, 2)), N'B', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:12:00.0000000' AS DateTime2), N'15', CAST(1.90 AS Decimal(15, 2)), N'A', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:18:00.0000000' AS DateTime2), N'1', CAST(1.91 AS Decimal(15, 2)), N'B', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:18:00.0000000' AS DateTime2), N'15', CAST(1.99 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:24:00.0000000' AS DateTime2), N'1', CAST(1.93 AS Decimal(15, 2)), N'B', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:24:00.0000000' AS DateTime2), N'15', CAST(1.93 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:30:00.0000000' AS DateTime2), N'1', CAST(1.85 AS Decimal(15, 2)), N'B', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:30:00.0000000' AS DateTime2), N'15', CAST(1.86 AS Decimal(15, 2)), N'A', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:36:00.0000000' AS DateTime2), N'1', CAST(1.75 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:36:00.0000000' AS DateTime2), N'15', CAST(1.96 AS Decimal(15, 2)), N'B', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:42:00.0000000' AS DateTime2), N'1', CAST(1.69 AS Decimal(15, 2)), N'A', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:42:00.0000000' AS DateTime2), N'15', CAST(1.97 AS Decimal(15, 2)), N'B', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:48:00.0000000' AS DateTime2), N'1', CAST(1.71 AS Decimal(15, 2)), N'A', N'2')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:48:00.0000000' AS DateTime2), N'15', CAST(1.89 AS Decimal(15, 2)), N'A', N'1')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:54:00.0000000' AS DateTime2), N'1', CAST(1.86 AS Decimal(15, 2)), N'C', N'3')

INSERT tmp ([Date_Time], [MonitorID], [Value], [Status1], [Status2]) VALUES (CAST(N'2022-06-01T01:54:00.0000000' AS DateTime2), N'15', CAST(2.00 AS Decimal(15, 2)), N'C', N'1')

Select *
From tmp


Date_Time
MonitorID
Value
Status1
Status2


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

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

2022-06-01 00:06:00
1
4.07
B
3

2022-06-01 00:06:00
15
2.13
A
1

2022-06-01 00:12:00
1
3.56
C
2

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

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

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

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

2022-06-01 00:24:00
15
2.17
B
1

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

2022-06-01 00:30:00
15
2.18
C
3

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

2022-06-01 00:36:00
15
2.16
B
1

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

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

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

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

2022-06-01 00:54:00
1
1.70
C
3

2022-06-01 00:54:00
15
1.87
C
2

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

2022-06-01 01:00:00
15
1.98
A
2

2022-06-01 01:06:00
1
1.64
B
3

2022-06-01 01:06:00
15
1.97
B
1

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

2022-06-01 01:12:00
15
1.90
A
3

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

2022-06-01 01:18:00
15
1.99
A
1

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

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

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

2022-06-01 01:30:00
15
1.86
A
2

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

2022-06-01 01:36:00
15
1.96
B
2

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

2022-06-01 01:42:00
15
1.97
B
1

2022-06-01 01:48:00
1
1.71
A
2

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

2022-06-01 01:54:00
1
1.86
C
3

2022-06-01 01:54:00
15
2.00
C
1


Select dateadd(hour, datediff(hour, 0, [Date_Time]), 0) as 'DateTimePerHour'
  ,MonitorID
  ,AVG(Value) AverageValue
  ,'?' Status1
  ,'?' Status2
  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
Status1
Status2


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,Status1,count(*) StatusCount
From tmp
group by dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status1


DateTimePerHour
MonitorID
Status1
StatusCount


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

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

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

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

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

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

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

2022-06-01 01:00:00.000
1
B
5

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

2022-06-01 01:00:00.000
15
A
6

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

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


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


DateTimePerHour
MonitorID
Status2
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
5

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

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

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

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

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

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

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

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


-- Status1
SELECT DateTimePerHour,MonitorID,SubTotal / Status1CountSum AS 'AverageValue',Status1
FROM (
SELECT DateTimePerHour,MonitorID,Status1,
SUM(Status1Count) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'Status1CountSum',
SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY Status1Count DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status1,COUNT(*) AS 'Status1Count',SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status1
) AS K
) AS Q
WHERE Q.NO = 1 


DateTimePerHour
MonitorID
AverageValue
Status1


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

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

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

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


-- Status2
SELECT DateTimePerHour,MonitorID,SubTotal / Status2CountSum AS 'AverageValue',Status2
FROM (
SELECT DateTimePerHour,MonitorID,Status2,
SUM(Status2Count) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'Status2CountSum',
SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY Status2Count DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status2,COUNT(*) AS 'Status2Count',SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status2
) AS K
) AS Q
WHERE Q.NO = 1 


DateTimePerHour
MonitorID
AverageValue
Status2


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

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

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

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


SELECT A.*,B.Status2
FROM (
-- Status1
SELECT DateTimePerHour,MonitorID,SubTotal / Status1CountSum AS 'AverageValue',Status1
FROM (
SELECT DateTimePerHour,MonitorID,Status1,
SUM(Status1Count) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'Status1CountSum',
SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY Status1Count DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status1,COUNT(*) AS 'Status1Count',SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status1
) AS K
) AS Q
WHERE Q.NO = 1 ) AS A
--
INNER JOIN (
-- Status2
SELECT DateTimePerHour,MonitorID,
-- SubTotal / Status2CountSum AS 'AverageValue',
Status2
FROM (
SELECT DateTimePerHour,MonitorID,Status2,
-- SUM(Status2Count) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'Status2CountSum',
-- SUM(ValuePart) OVER (PARTITION BY DateTimePerHour,MonitorID) AS 'SubTotal',
ROW_NUMBER() OVER (PARTITION BY DateTimePerHour,MonitorID ORDER BY Status2Count DESC) AS 'NO'
FROM (
SELECT dateadd(hour, datediff(hour, 0, [Date_Time]), 0) AS 'DateTimePerHour',
MonitorID,Status2,COUNT(*) AS 'Status2Count'
-- ,SUM(Value) AS 'ValuePart'
FROM tmp
GROUP BY dateadd(hour, datediff(hour, 0, [Date_Time]), 0),MonitorID,Status2
) AS K
) AS Q
WHERE Q.NO = 1 
) AS B ON B.DateTimePerHour = A.DateTimePerHour AND B.MonitorID = A.MonitorID
-- AND B.AverageValue = A.AverageValue


DateTimePerHour
MonitorID
AverageValue
Status1
Status2


2022-06-01 00:00:00.000
1
2.837000
C
2

2022-06-01 00:00:00.000
15
2.075000
C
1

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

2022-06-01 01:00:00.000
15
1.945000
A
1
Date_Time	MonitorID	Value	Status1	Status2
2022-06-01 00:00:00	1	4.36	A	1
2022-06-01 00:00:00	15	2.10	B	1
2022-06-01 00:06:00	1	4.07	B	3
2022-06-01 00:06:00	15	2.13	A	1
2022-06-01 00:12:00	1	3.56	C	2
2022-06-01 00:12:00	15	2.09	C	3
2022-06-01 00:18:00	1	3.23	C	1
2022-06-01 00:18:00	15	2.23	C	2
2022-06-01 00:24:00	1	2.98	A	3
2022-06-01 00:24:00	15	2.17	B	1
2022-06-01 00:30:00	1	2.61	A	2
2022-06-01 00:30:00	15	2.18	C	3
2022-06-01 00:36:00	1	2.13	C	2
2022-06-01 00:36:00	15	2.16	B	1
2022-06-01 00:42:00	1	1.94	B	2
2022-06-01 00:42:00	15	2.01	A	2
2022-06-01 00:48:00	1	1.79	B	1
2022-06-01 00:48:00	15	1.81	A	1
2022-06-01 00:54:00	1	1.70	C	3
2022-06-01 00:54:00	15	1.87	C	2
2022-06-01 01:00:00	1	1.60	A	1
2022-06-01 01:00:00	15	1.98	A	2
2022-06-01 01:06:00	1	1.64	B	3
2022-06-01 01:06:00	15	1.97	B	1
2022-06-01 01:12:00	1	1.76	B	2
2022-06-01 01:12:00	15	1.90	A	3
2022-06-01 01:18:00	1	1.91	B	3
2022-06-01 01:18:00	15	1.99	A	1
2022-06-01 01:24:00	1	1.93	B	2
2022-06-01 01:24:00	15	1.93	A	1
2022-06-01 01:30:00	1	1.85	B	3
2022-06-01 01:30:00	15	1.86	A	2
2022-06-01 01:36:00	1	1.75	A	1
2022-06-01 01:36:00	15	1.96	B	2
2022-06-01 01:42:00	1	1.69	A	3
2022-06-01 01:42:00	15	1.97	B	1
2022-06-01 01:48:00	1	1.71	A	2
2022-06-01 01:48:00	15	1.89	A	1
2022-06-01 01:54:00	1	1.86	C	3
2022-06-01 01:54:00	15	2.00	C	1
DateTimePerHour	MonitorID	AverageValue	Status1	Status2
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	?	?