MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `aktualizace_ciselniku` (
  `id_aktualizace_ciselniku_el` int(11) NOT NULL AUTO_INCREMENT,
  `aktualizace_ciselniku_ean` varchar(20) COLLATE utf8_czech_ci NOT NULL,
  `aktualizace_ciselniku_datum_od` date NOT NULL,
  `aktualizace_ciselniku_datum_do` date NOT NULL,
  `aktualizace_ciselniku_typ_mereni` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  `aktualizace_ciselniku_poskytovatel_dat` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  `aktualizace_ciselniku_sit` varchar(5) COLLATE utf8_czech_ci DEFAULT NULL,
  `aktualizace_ciselniku_tdd` varchar(8) COLLATE utf8_czech_ci DEFAULT NULL,
  `aktualizace_ciselniku_teplotni_oblast` varchar(22) COLLATE utf8_czech_ci DEFAULT NULL,
  `aktualizace_ciselniku_primarni_dodavatel` varchar(13) COLLATE utf8_czech_ci DEFAULT NULL,
  `aktualizace_ciselniku_subjekt_zuctovani` varchar(13) COLLATE utf8_czech_ci DEFAULT NULL,
  `aktualizace_ciselniku_odhad_rocni_spotreby` decimal(28,20) DEFAULT '0.00000000000000000000',
  `nazev_import_davky` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  PRIMARY KEY (`id_aktualizace_ciselniku_el`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=5 ;
INSERT INTO `aktualizace_ciselniku` (`id_aktualizace_ciselniku_el`, `aktualizace_ciselniku_ean`, `aktualizace_ciselniku_datum_od`, `aktualizace_ciselniku_datum_do`, `aktualizace_ciselniku_typ_mereni`, `aktualizace_ciselniku_poskytovatel_dat`, `aktualizace_ciselniku_sit`, `aktualizace_ciselniku_tdd`, `aktualizace_ciselniku_teplotni_oblast`, `aktualizace_ciselniku_primarni_dodavatel`, `aktualizace_ciselniku_subjekt_zuctovani`, `aktualizace_ciselniku_odhad_rocni_spotreby`, `nazev_import_davky`) VALUES
(1, '800182400301980001', '2019-10-04', '2027-09-30', 'C', '8591824003008', '31', 'TDD č. 5', 'Region Praha', '8591824264935', '8591824219133', 4424.00000000000000000000, 'SYSTEM_IMPORT'),
(2, '800182400202510002', '2019-08-23', '9999-12-31', 'C', '8591824048108', '21', 'TDD č. 4', 'Region Česká republika', '8591824264935', '8591824219133', 2446.00000000000000000000, 'SYSTEM_iMPORT'),
(3, '800182400208780003', '2020-01-31', '2027-09-30', 'C', '8591824048108', '21', 'TDD č. 4', 'Region Česká republika', '8591824264935', '8591824219133', 756.00000000000000000000, 'SYSTEM_IMPORT'),
(4, '800182400606080004', '2020-03-05', '9999-12-31', 'C', '8591824071502', '61', 'TDD č. 4', 'Region Česká republika', '8591824264935', '8591824219133', 1208.00000000000000000000, 'SYSTEM_IMPORT'),
(5, '800182400702560005', '2020-04-23', '9999-12-31', 'C', '8591824071502', '71', 'TDD č. 5', 'Region Východní Čechy', '8591824264935', '8591824264935', 2437.00000000000000000000, 'SYSTEM_IMPORT');
CREATE TABLE IF NOT EXISTS `normalizovane_tdd` (
  `id_normalizovanetdd` int(11) NOT NULL AUTO_INCREMENT,
  `den` varchar(10) COLLATE utf8_czech_ci NOT NULL,
  `hodina` varchar(2) COLLATE utf8_czech_ci NOT NULL,
  `por_cislo_hod_v_roce` varchar(5) COLLATE utf8_czech_ci NOT NULL,
  `tdd_tdd1cr` decimal(8,6) NOT NULL,
  `tdd_tdd2cr` decimal(8,6) NOT NULL,
  `tdd_tdd3cr` decimal(8,6) NOT NULL,
  `tdd_tdd4cr` decimal(8,6) NOT NULL,
  `tdd_tdd5jiznicechy` decimal(8,6) NOT NULL,
  `tdd_tdd5jiznimorava` decimal(8,6) NOT NULL,
  `tdd_tdd5praha` decimal(8,6) NOT NULL,
  `tdd_tdd5severnicechy` decimal(8,6) NOT NULL,
  `tdd_tdd5severnimorava` decimal(8,6) NOT NULL,
  `tdd_tdd5strednicechy` decimal(8,6) NOT NULL,
  `tdd_tdd5vychodnicechy` decimal(8,6) NOT NULL,
  `tdd_tdd5zapadnicechy` decimal(8,6) NOT NULL,
  `tdd_tdd6cr` decimal(8,6) NOT NULL,
  `tdd_tdd7cr` decimal(8,6) NOT NULL,
  `tdd_tdd8cr` decimal(8,6) NOT NULL,
  PRIMARY KEY (`id_normalizovanetdd`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=10 ;
INSERT INTO `normalizovane_tdd` (`id_normalizovanetdd`, `den`, `hodina`, `por_cislo_hod_v_roce`, `tdd_tdd1cr`, `tdd_tdd2cr`, `tdd_tdd3cr`, `tdd_tdd4cr`, `tdd_tdd5jiznicechy`, `tdd_tdd5jiznimorava`, `tdd_tdd5praha`, `tdd_tdd5severnicechy`, `tdd_tdd5severnimorava`, `tdd_tdd5strednicechy`, `tdd_tdd5vychodnicechy`, `tdd_tdd5zapadnicechy`, `tdd_tdd6cr`, `tdd_tdd7cr`, `tdd_tdd8cr`) VALUES
(1, '2020-01-01', '1', '1', 0.395480, 0.637120, 0.509510, 0.438910, 0.421940, 0.438610, 0.435840, 0.453100, 0.509750, 0.532400, 0.399200, 0.519100, 0.589500, 0.620270, 1.000000),
(9, '2020-01-01', '9', '9', 0.365950, 0.420690, 0.524030, 0.384360, 0.363070, 0.277290, 0.295750, 0.307030, 0.362110, 0.290430, 0.340740, 0.332580, 0.407180, 0.631180, 0.000000),
(10, '2020-01-01', '10', '10', 0.381180, 0.418050, 0.514600, 0.497460, 0.428830, 0.339850, 0.299580, 0.344330, 0.412270, 0.352910, 0.412740, 0.388070, 0.461280, 0.748400, 0.000000);
select t1.aktualizace_ciselniku_ean, t2.den, t2.hodina,
       sum(case when t1.aktualizace_ciselniku_tdd = 'TDD č. 5' and aktualizace_ciselniku_teplotni_oblast = 'Region Praha' then t2.tdd_tdd5praha * t1.aktualizace_ciselniku_odhad_rocni_spotreby
                when t1.aktualizace_ciselniku_tdd = 'TDD č. 5' and aktualizace_ciselniku_teplotni_oblast = 'Region Východní Čechy' then t2.tdd_tdd5vychodnicechy * t1.aktualizace_ciselniku_odhad_rocni_spotreby
                when t1.aktualizace_ciselniku_tdd = 'TDD č. 4' then t2.tdd_tdd5vychodnicechy * t1.aktualizace_ciselniku_odhad_rocni_spotreby
                else 0
           end) as aktualizace_ciselniku_odhad_rocni_spotreby                
from aktualizace_ciselniku t1  JOIN
     normalizovane_tdd t2
     on t2.den >= t1.aktualizace_ciselniku_datum_od and t2.den <= t1.aktualizace_ciselniku_datum_do
group by t1.aktualizace_ciselniku_ean, t2.den, t2.hodina;


aktualizace_ciselniku_ean
den
hodina
aktualizace_ciselniku_odhad_rocni_spotreby


800182400301980001
2020-01-01
10
1325.34192000000000000000000000

800182400301980001
2020-01-01
9
1308.39800000000000000000000000

800182400301980001
2020-01-01
1
1928.15616000000000000000000000

800182400202510002
2020-01-01
10
1009.56204000000000000000000000

800182400202510002
2020-01-01
9
833.45004000000000000000000000

800182400202510002
2020-01-01
1
976.44320000000000000000000000
aktualizace_ciselniku_ean	den	hodina	aktualizace_ciselniku_odhad_rocni_spotreby
800182400301980001	2020-01-01	10	1325.34192000000000000000000000
800182400301980001	2020-01-01	9	1308.39800000000000000000000000
800182400301980001	2020-01-01	1	1928.15616000000000000000000000
800182400202510002	2020-01-01	10	1009.56204000000000000000000000
800182400202510002	2020-01-01	9	833.45004000000000000000000000
800182400202510002	2020-01-01	1	976.44320000000000000000000000