By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (
Time TIMESTAMP,
Val_1a INT,
Val_2a INT,
Val_3a INT);
INSERT INTO table1 VALUES
('2021-11-06 13:59:53',15,10,35),
('2021-11-06 14:00:02',12,15,34),
('2021-11-06 14:05:25',11,13,35),
('2021-11-06 14:05:35',11,17,36);
CREATE TABLE table2 (
Time TIMESTAMP,
Val_1b INT,
Val_2b INT,
Val_3b INT);
INSERT INTO table2 VALUES
('2021-11-06 11:00:00',2,15,30),
('2021-11-06 12:00:00',8,12,32),
('2021-11-06 13:00:00',12,11,35);
SELECT * FROM table1;
SELECT * FROM table2;
| Time | Val_1a | Val_2a | Val_3a |
|---|---|---|---|
| 2021-11-06 13:59:53 | 15 | 10 | 35 |
| 2021-11-06 14:00:02 | 12 | 15 | 34 |
| 2021-11-06 14:05:25 | 11 | 13 | 35 |
| 2021-11-06 14:05:35 | 11 | 17 | 36 |
| Time | Val_1b | Val_2b | Val_3b |
|---|---|---|---|
| 2021-11-06 11:00:00 | 2 | 15 | 30 |
| 2021-11-06 12:00:00 | 8 | 12 | 32 |
| 2021-11-06 13:00:00 | 12 | 11 | 35 |
SELECT t1.dtm, t1.Val_2a2, t1.Val_2a1, t2.Val_1b2,
CASE WHEN Val_2a2 > Val_2a1 THEN Val_2a2-Val_2a1+Val_1b2 ELSE 0 END AS ValF
FROM
(SELECT DATE_FORMAT(time, '%Y-%m-%d %H:00:00') dtm,
SUBSTRING_INDEX(GROUP_CONCAT(Val_2a ORDER BY time),',',1) Val_2a1 ,
SUBSTRING_INDEX(GROUP_CONCAT(Val_2a ORDER BY time DESC),',',1) Val_2a2
FROM table1
GROUP BY dtm) t1
LEFT JOIN
(SELECT DATE(time) dtm,
SUBSTRING_INDEX(GROUP_CONCAT(Val_1b ORDER BY time DESC),',',1) Val_1b2
FROM table2
GROUP BY dtm) t2
ON DATE(t1.dtm)=t2.dtm;
| dtm | Val_2a2 | Val_2a1 | Val_1b2 | ValF |
|---|---|---|---|---|
| 2021-11-06 13:00:00 | 10 | 10 | 12 | 0 |
| 2021-11-06 14:00:00 | 17 | 15 | 12 | 14 |