By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `mdc_meters_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msn` varchar(100) DEFAULT NULL,
`kwh_t` varchar(100) DEFAULT NULL,
`data_date_time` datetime DEFAULT NULL,
`s_type` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52702 DEFAULT CHARSET=latin1;
/*Data for the table `mdc_meters_data` */
insert into `mdc_meters_data`(`id`,`msn`,`kwh_t`,`data_date_time`,`s_type`) values (49641,'4A60193390662','2068.3','2020-11-01 00:02:17','WAPDA'),
(49642,'00209701','1476.59','2020-11-01 00:02:47','Sync Meter'),(49643,'00209702','1389.79','2020-11-01 00:03:17','Sync Meter'),(49644,'4A60193390662','2068.3','2020-11-01 00:04:57','WAPDA'),(49645,'00209701','1476.6','2020-11-01 00:05:28','Sync Meter'),(49646,'00209702','1389.81','2020-11-01 00:05:58','Sync Meter'),(49647,'4A60193390662','2068.3','2020-11-01 00:07:38','WAPDA'),(49648,'00209701','1476.6','2020-11-01 00:08:08','Sync Meter'),(49649,'00209702','1389.81','2020-11-01 00:08:38','Sync Meter'),(49650,'4A60193390662','2068.3','2020-11-01 00:10:19','WAPDA'),(49651,'00209701','1476.6','2020-11-01 00:10:49','Sync Meter'),(49652,'00209702','1389.82','2020-11-01 00:11:19','Sync Meter'),(49653,'4A60193390663','1000.3','2020-11-01 00:12:59','Generator'),(49654,'00209701','1476.61','2020-11-01 00:13:30','Sync Meter'),(49655,'00209702','1389.83','2020-11-01 00:14:00','Sync Meter'),(49656,'4A60193390663','1000.3','2020-11-01 00:15:40','Generator'),(49657,'00209701','1476.61','2020-11-01 00:16:10','Sync Meter'),(49658,'00209702','1389.84','2020-11-01 00:16:40','Sync Meter'),(49659,'4A60193390663','1000.3','2020-11-01 00:18:20','Generator'),(49660,'00209701','1476.61','2020-11-01 00:18:51','Sync Meter'),(49661,'00209702','1389.84','2020-11-01 00:19:21','Sync Meter'),(49662,'4A60193390663','1000.3','2020-11-01 00:21:01','Generator'),(49663,'00209701','1476.61','2020-11-01 00:21:31','Sync Meter'),(49664,'00209702','1389.85','2020-11-01 00:22:01','Sync Meter'),(49665,'4A60193390662','2068.3','2020-11-01 00:23:42','WAPDA'),(49666,'00209701','1476.62','2020-11-01 00:24:12','Sync Meter'),(49667,'00209702','1389.86','2020-11-01 00:24:42','Sync Meter'),(49668,'4A60193390662','2068.3','2020-11-01 00:26:22','WAPDA'),(49669,'00209701','1476.63','2020-11-01 00:26:53','Sync Meter'),(49670,'00209702','1389.88','2020-11-01 00:27:23','Sync Meter'),(49671,'4A60193390662','2068.3','2020-11-01 00:29:03','WAPDA'),(49672,'00209701','1476.63','2020-11-01 00:29:33','Sync Meter'),(49673,'00209702','1389.88','2020-11-01 00:30:03','Sync Meter'),(49674,'4A60193390662','2068.3','2020-11-01 00:31:44','WAPDA')
SELECT msn,
s_type,
MAX(kwh_t) AS max_kwh,
MIN(data_date_time) AS min_date_time,
MAX(data_date_time) AS max_date_time
FROM (
SELECT md.*,
@rn := @rn + 1 AS rn,
@rst := CASE
WHEN @st = s_type THEN @rst + 1
WHEN @st := s_type THEN 1
ELSE 1
END AS rst
FROM (
SELECT *
FROM mdc_meters_data
WHERE s_type != 'Sync Meter'
AND data_date_time >= '2020-11-01'
AND data_date_time < '2020-11-02'
ORDER BY data_date_time
) md
CROSS JOIN (SELECT @rn := 0, @rst := 0, @st := '') init
) m
WHERE s_type = 'WAPDA'
GROUP BY msn, rn - rst, DATE(data_date_time), HOUR(data_date_time)
ORDER BY msn, min_date_time
| msn | s_type | max_kwh | min_date_time | max_date_time |
|---|---|---|---|---|
| 4A60193390662 | WAPDA | 2068.3 | 2020-11-01 00:02:17 | 2020-11-01 00:10:19 |
| 4A60193390662 | WAPDA | 2068.3 | 2020-11-01 00:23:42 | 2020-11-01 00:31:44 |
SELECT msn,
s_type,
MAX(kwh_t) AS max_kwh,
MIN(data_date_time) AS min_date_time,
MAX(data_date_time) AS max_date_time
FROM (
SELECT md.*,
@rn := @rn + 1 AS rn,
@rst := CASE
WHEN @st = s_type THEN @rst + 1
WHEN @st := s_type THEN 1
ELSE 1
END AS rst
FROM (
SELECT *
FROM mdc_meters_data
WHERE s_type != 'Sync Meter'
AND data_date_time >= '2020-11-01'
AND data_date_time < '2020-11-02'
ORDER BY data_date_time
) md
CROSS JOIN (SELECT @rn := 0, @rst := 0, @st := '') init
) m
WHERE s_type = 'Generator'
GROUP BY msn, rn - rst, DATE(data_date_time), HOUR(data_date_time)
ORDER BY msn, min_date_time
| msn | s_type | max_kwh | min_date_time | max_date_time |
|---|---|---|---|---|
| 4A60193390663 | Generator | 1000.3 | 2020-11-01 00:12:59 | 2020-11-01 00:21:01 |