-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathQUERY_Out_BB3.inc
66 lines (66 loc) · 20.9 KB
/
QUERY_Out_BB3.inc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
DROP TABLE if exists `El_Gen`;
CREATE TABLE `El_Gen` (`Case1` TEXT, `Y1` TEXT, `Area` TEXT, `Technology` TEXT, `Season` TEXT, `Term` TEXT, `Generation_MW` REAL);
INSERT INTO EL_Gen(Case1, Y1, Area, Technology, Season, Term, Generation_MW) SELECT VGE_T, VGE_T2, VGE_T3, VGE_T4, VGE_T5, VGE_T6, level FROM VGE_T WHERE level >0;
DROP TABLE if exists `Electricity generation - Term`;
CREATE TABLE `Electricity generation - Term` (`Case` TEXT,`Year`TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT,`Technology Type` TEXT,`Technology` TEXT, `Season` TEXT, `Term` TEXT, `MW` REAL, `MWh`REAL, `GJ` REAL);
INSERT INTO `Electricity generation - Term`(`Case`, Year, Country, Region, Area, Fuel, `Technology Type`, Technology, Season, Term, MW, MWh, GJ) SELECT [El_Gen].Case1 AS [Case], [El_Gen].Y1 AS [Year], ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, [El_Gen].Area AS Area, IGF.IGF4 AS Fuel, GDATA.value AS [Technology Type`], [El_Gen].Technology AS Technology, [El_Gen].Season AS Season, [El_Gen].Term AS Term, Sum([El_Gen].Generation_MW) AS MW, Sum([El_Gen].[Generation_MW]*[IHOURSINST].[value]) AS MWh, Sum([El_Gen].[Generation_MW]*[IHOURSINST].[value])*3.6 AS GJ FROM IHOURSINST INNER JOIN (((RRRAAA INNER JOIN [El_Gen] ON (RRRAAA.RRRAAA = [El_Gen].Case1) AND (RRRAAA.RRRAAA4 = [El_Gen].Area)) INNER JOIN ICA ON (ICA.ICA4 = [El_Gen].AREA) AND ([El_Gen].Case1 = ICA.ICA)) INNER JOIN IGF ON (IGF.IGF3 = [El_Gen].Technology) AND ([El_Gen].Case1 = IGF.IGF)) ON (IHOURSINST.IHOURSINST4 = [El_Gen].Term) AND (IHOURSINST.IHOURSINST3 = [El_Gen].Season) AND (IHOURSINST.IHOURSINST = [El_Gen].Case1) INNER JOIN GDATA ON (GDATA.GDATA = [El_Gen].Case1) AND (GDATA.GDATA3 = [El_Gen].Technology) AND (GDATA.GDATA4 = "GDTYPE") GROUP BY [El_Gen].Case1, [El_Gen].Y1, ICA.ICA3, RRRAAA.RRRAAA3, [El_Gen].Area, IGF.IGF4, [El_Gen].Technology, [El_Gen].Season, [El_Gen].Term, [El_Gen].Generation_MW;
DROP TABLE if exists `Electricity generation - Annual`;
CREATE TABLE `Electricity generation - Annual` (`Case` TEXT, `Year` TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT, `Technology Type` TEXT, `Technology` TEXT, `TWh`REAL, `PJ` REAL);
INSERT INTO `Electricity generation - annual`(`Case`, Year, Country, Region, Area, Fuel,`Technology Type`, Technology, TWh, PJ) SELECT [Electricity generation - Term].`Case`, [Electricity generation - Term].Year,[Electricity generation - Term].Country, [Electricity generation - Term].Region, [Electricity generation - Term].Area, [Electricity generation - Term].Fuel, [Electricity generation - Term].[Technology Type], [Electricity generation - Term].Technology, Sum([MWh]/1000000) AS TWh, Sum([GJ]/1000000) AS PJ FROM [Electricity generation - Term] GROUP BY [Electricity generation - Term].`Case`, [Electricity generation - Term].Year,[Electricity generation - Term].Country, [Electricity generation - Term].Region, [Electricity generation - Term].Area, [Electricity generation - Term].Fuel, [Electricity generation - Term].[Technology Type],[Electricity generation - Term].Technology;
DROP TABLE if exists `Electricity_price`;
CREATE TABLE `Electricity_price`(`Case`TEXT, `Year1` TEXT, `Country` TEXT, `Region` TEXT, `Season` TEXT, `Term` TEXT, `OMONEY/MWh` REAL);
INSERT INTO `Electricity_price`(`Case`,`Year1`,`Country`,`Region`,`Season`,`Term`,`OMONEY/MWh`) SELECT QEEQ.QEEQ AS [`Case`], QEEQ.QEEQ2 AS [Year1], CCCRRR.CCCRRR3 AS Country, QEEQ.QEEQ3 AS Region, QEEQ.QEEQ4 AS Season, QEEQ.QEEQ5 AS Term, Avg([QEEQ].[marginal]/[IHOURSINST].[value]*[OMONEY].[value]) AS [OMONEY/MWh] FROM CCCRRR INNER JOIN (OMONEY INNER JOIN (QEEQ INNER JOIN IHOURSINST ON (QEEQ.QEEQ5 = IHOURSINST.IHOURSINST4) AND (QEEQ.QEEQ = IHOURSINST.IHOURSINST) AND (QEEQ.QEEQ4 = IHOURSINST.IHOURSINST3)) ON OMONEY.OMONEY = QEEQ.QEEQ) ON (CCCRRR.CCCRRR4 = QEEQ.QEEQ3) AND (CCCRRR.CCCRRR = QEEQ.QEEQ) GROUP BY QEEQ.QEEQ, QEEQ.QEEQ2, CCCRRR.CCCRRR3, QEEQ.QEEQ3, QEEQ.QEEQ4, QEEQ.QEEQ5;
DROP TABLE if exists `Heat_Gen`;
CREATE TABLE `Heat_Gen` (`Case1` TEXT, `Y1` TEXT, `Area` TEXT, `Technology` TEXT, `Season` TEXT, `Term` TEXT, `Generation_MW` REAL);
INSERT INTO Heat_Gen(Case1, Y1, Area, Technology, Season, Term, Generation_MW) SELECT VGH_T, VGH_T2, VGH_T3, VGH_T4, VGH_T5, VGH_T6, level FROM VGH_T WHERE level >0;
DROP TABLE if exists `Heat Generation - Term`;
CREATE TABLE `Heat Generation - Term` (`Case` TEXT, `Year1` TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT, `Technology` TEXT, `Season` TEXT, `Term` TEXT, `MW` REAL, `MWh`REAL, `GJ` REAL);
INSERT INTO `Heat generation - Term`(`Case`, Year1, Country, Region, Area, Fuel, Technology, Season, Term, MW, MWh, GJ) SELECT [Heat_Gen].Case1 AS [Case], [Heat_Gen].Y1 AS [Year1], ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, [Heat_Gen].Area AS Area, IGF.IGF4 AS Fuel, [Heat_Gen].Technology AS Technology, [Heat_Gen].Season AS Season, [Heat_Gen].Term AS Term, Sum([Heat_Gen].Generation_MW) AS MW, Sum([Heat_Gen].[Generation_MW]*[IHOURSINST].[value]) AS MWh, Sum([Heat_Gen].[Generation_MW]*[IHOURSINST].[value])*3.6 AS GJ FROM IHOURSINST INNER JOIN (((RRRAAA INNER JOIN [Heat_Gen] ON (RRRAAA.RRRAAA = [Heat_Gen].Case1) AND (RRRAAA.RRRAAA4 = [Heat_Gen].Area)) INNER JOIN ICA ON (ICA.ICA4 = [Heat_Gen].AREA) AND ([Heat_Gen].Case1 = ICA.ICA)) INNER JOIN IGF ON (IGF.IGF3 = [Heat_Gen].Technology) AND ([Heat_Gen].Case1 = IGF.IGF)) ON (IHOURSINST.IHOURSINST4 = [Heat_Gen].Term) AND (IHOURSINST.IHOURSINST3 = [Heat_Gen].Season) AND (IHOURSINST.IHOURSINST = [Heat_Gen].Case1) GROUP BY [Heat_Gen].Case1, [Heat_Gen].Y1, ICA.ICA3, RRRAAA.RRRAAA3, [Heat_Gen].Area, IGF.IGF4, [Heat_Gen].Technology, [Heat_Gen].Season, [Heat_Gen].Term, [Heat_Gen].Generation_MW;
DROP TABLE if exists `Heat Generation - Annual`;
CREATE TABLE `Heat Generation - Annual` (`Case` TEXT, `Year1` TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT, `Technology` TEXT, `TWh`REAL, `PJ` REAL);
INSERT INTO `Heat Generation - Annual`(`Case`, Year1, Country, Region, Area, Fuel, Technology, TWh, PJ) SELECT [Heat Generation - Term].`Case`, [Heat Generation - Term].Year1,[Heat Generation - Term].Country, [Heat Generation - Term].Region, [Heat Generation - Term].Area, [Heat Generation - Term].Fuel, [Heat Generation - Term].Technology, Sum([MWh]/1000000) AS TWh, Sum([GJ]/1000000) AS PJ FROM [Heat Generation - Term] GROUP BY [Heat Generation - Term].`Case`, [Heat Generation - Term].Year1, [Heat Generation - Term].Country, [Heat Generation - Term].Region, [Heat Generation - Term].Area, [Heat Generation - Term].Fuel, [Heat Generation - Term].Technology;
DROP TABLE if exists `Fuel_Con`;
CREATE TABLE `Fuel_Con`(`Case1` TEXT, `Year1`TEXT, `Area` TEXT, `Technology` TEXT, `Season` TEXT, `Term` TEXT, `MW` REAL);
INSERT INTO Fuel_Con(Case1, Year1, Area, Technology, Season,Term, MW) SELECT VGF_T, VGF_T2, VGF_T3, VGF_T4, VGF_T5, VGF_T6, level FROM VGF_T WHERE level >0;
DROP TABLE if exists `Fuel Consumption - Term`;
CREATE TABLE `Fuel Consumption - Term` (`Case` TEXT, `Year1` TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT, `Technology` TEXT, `Season` TEXT, `Term` TEXT, `MJ/s` REAL, `MWh`REAL, `GJ` REAL);
INSERT INTO `Fuel Consumption - Term`(`Case`, Year1, Country, Region, Area, Fuel, Technology, Season, Term,`MJ/s`, MWh, GJ) SELECT Fuel_Con.Case1 AS `Case`, Fuel_Con.Year1 AS Year1, ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, Fuel_Con.Area AS Area, IGF.IGF4 AS `Fuel`, Fuel_Con.Technology AS Technology, Fuel_Con.Season AS Season, Fuel_Con.Term AS Term, Sum([Fuel_Con].MW) AS `MJ/s`, Sum([Fuel_Con].[MW]*[IHOURSINST].[value]) AS MWh, Sum([Fuel_Con].[MW]*[IHOURSINST].[value])*3.6 AS GJ FROM IGF INNER JOIN (IHOURSINST INNER JOIN (RRRAAA INNER JOIN (ICA INNER JOIN [Fuel_Con] ON (ICA.ICA4 = [Fuel_Con].Area) AND (ICA.ICA = [Fuel_Con].Case1)) ON (RRRAAA.RRRAAA4 = [Fuel_Con].Area) AND (RRRAAA.RRRAAA = [Fuel_Con].Case1)) ON (IHOURSINST.IHOURSINST4 = [Fuel_Con].Term) AND (IHOURSINST.IHOURSINST3 = [Fuel_Con].Season) AND (IHOURSINST.IHOURSINST = [Fuel_Con].Case1)) ON (IGF.IGF3 = [Fuel_Con].Technology) AND (IGF.IGF = [Fuel_Con].Case1)GROUP BY [Fuel_Con].Case1, [Fuel_Con].Year1, ICA.ICA3, RRRAAA.RRRAAA3, [Fuel_Con].Area, IGF.IGF4, [Fuel_Con].Technology, [Fuel_Con].Season, [Fuel_Con].Term;
DROP TABLE if exists `Fuel Consumption - Annual`;
CREATE TABLE `Fuel Consumption - Annual` (`Case` TEXT, `Year1` TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT, `Technology` TEXT, `TWh`REAL, `PJ` REAL);
INSERT INTO `Fuel Consumption - Annual`(`Case`, Year1, Country, Region, Area, Fuel, Technology, TWh, PJ) SELECT [Fuel Consumption - Term].`Case`, [Fuel Consumption - Term].Year1,[Fuel Consumption - Term].Country, [Fuel Consumption - Term].Region, [Fuel Consumption - Term].Area, [Fuel Consumption - Term].Fuel, [Fuel Consumption - Term].Technology, Sum([MWh]/1000000) AS TWh, Sum([GJ]/1000000) AS PJ FROM [Fuel Consumption - Term] GROUP BY [Fuel Consumption - Term].`Case`, [Fuel Consumption - Term].Year1, [Fuel Consumption - Term].Country, [Fuel Consumption - Term].Region, [Fuel Consumption - Term].Area, [Fuel Consumption - Term].Fuel, [Fuel Consumption - Term].Technology;
DROP TABLE if exists `Emissions - Term`;
CREATE TABLE `Emissions - Term`(`Case` TEXT, `Year1`TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT,`Fuel` TEXT, `Technology` TEXT, `Season` TEXT, `Term` TEXT, `CO2 t/h` REAL,`CO2 tons` REAL, `SO2 t/h` REAL,`SO2 tons` REAL, `N2O t/h` REAL,`N2O tons` REAL);
INSERT INTO `Emissions - Term`(`Case`,Year1,Country,Region,Area,`Fuel`,Technology,Season, `Term`, `CO2 t/h`,`CO2 tons`, `SO2 t/h`,`SO2 tons`, `N2O t/h`,`N2O tons`) SELECT Fuel_con.Case1 AS `Case`, Fuel_con.Year1 AS Year1, ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, Fuel_con.Area AS Area, IGF.IGF4 AS Fuel, Fuel_con.Technology AS Technology, Fuel_con.Season AS Season, Fuel_con.Term AS Term, Sum([Fuel_con].[MW]*[IM_CO2].[value])*3.6/1000 AS `CO2 t/h`, Sum([Fuel_con].[MW]*[IHOURSINST].[value]*[IM_CO2].[value])*3.6/1000 AS `CO2 tons`,Sum([Fuel_con].[MW]*[IM_SO2].[value])*3.6/1000 AS `SO2 t/h`, Sum([Fuel_con].[MW]*[IHOURSINST].[value]*[IM_SO2].[value])*3.6/1000 AS `SO2 tons`,Sum([Fuel_con].[MW]*[IM_N2O].[value])*3.6/1000 AS `N2O t/h`, Sum([Fuel_con].[MW]*[IHOURSINST].[value]*[IM_N2O].[value])*3.6/1000 AS `N2O tons`FROM (IGF INNER JOIN (IHOURSINST INNER JOIN (RRRAAA INNER JOIN (ICA INNER JOIN [Fuel_con] ON (ICA.ICA4 = [Fuel_con].Area) AND (ICA.ICA = [Fuel_con].Case1)) ON (RRRAAA.RRRAAA4 = [Fuel_con].Area) AND (RRRAAA.RRRAAA = [Fuel_con].Case1)) ON (IHOURSINST.IHOURSINST4 = [Fuel_con].Term) AND (IHOURSINST.IHOURSINST3 = [Fuel_con].Season) AND (IHOURSINST.IHOURSINST = [Fuel_con].Case1)) ON (IGF.IGF3 = [Fuel_con].Technology) AND (IGF.IGF = [Fuel_con].Case1)) INNER JOIN IM_SO2 ON (IM_SO2.IM_SO23 = [Fuel_con].Technology) AND ([Fuel_con].Case1 = IM_SO2.IM_SO2) INNER JOIN IM_CO2 ON (IM_CO2.IM_CO23 = [Fuel_con].Technology) AND ([Fuel_con].Case1 = IM_CO2.IM_CO2) INNER JOIN IM_N2O ON (IM_N2O.IM_N2O3 = [Fuel_con].Technology) AND ([Fuel_con].Case1 = IM_N2O.IM_N2O)GROUP BY [Fuel_con].Case1, [Fuel_con].Year1, ICA.ICA3, RRRAAA.RRRAAA3, [Fuel_con].Area, IGF.IGF4, [Fuel_con].Technology, [Fuel_con].Season, [Fuel_con].Term;
DROP TABLE if exists `Emissions - Annual`;
CREATE TABLE `Emissions - Annual`(`Case` TEXT, `Year1`TEXT,`Country` TEXT, `Region` TEXT, `Area` TEXT,`Fuel` TEXT, `Technology` TEXT,`CO2 kilotons` REAL,`CO2 megatons` REAL, `SO2 kilotons` REAL,`SO2 megatons` REAL, `N2O kilotons` REAL,`N2O megatons` REAL);
INSERT INTO `Emissions - Annual`(`Case`,Year1,Country,Region, Area,Fuel,Technology,`CO2 kilotons`,`CO2 megatons`, `SO2 kilotons`,`SO2 megatons`, `N2O kilotons`,`N2O megatons`) SELECT [Emissions - Term].`Case`, [Emissions - Term].Year1, [Emissions - Term].Country, [Emissions - Term].Region, [Emissions - Term].Area, [Emissions - Term].Fuel, [Emissions - Term].Technology, Sum([CO2 Tons]/1000) AS `CO2 kilotons`, Sum([CO2 Tons]/1000000) AS `CO2 megatons`, Sum([SO2 Tons]/1000) AS `SO2 kilotons`, Sum([SO2 Tons]/1000000) AS `SO2 megatons`, Sum([N2O Tons]/1000) AS `N2O kilotons`, Sum([N2O Tons]/1000000) AS `N2O megatons` FROM [Emissions - Term] GROUP BY [Emissions - Term].`Case`, [Emissions - Term].Year1, [Emissions - Term].Country, [Emissions - Term].Region, [Emissions - Term].Area, [Emissions - Term].Fuel, [Emissions - Term].Technology;
DROP TABLE if exists `Heat Storage volume`;
CREATE TABLE `Heat Storage volume`(`Case` TEXT, `Year`TEXT,`Country` TEXT, `Region` TEXT, `Area` TEXT,`Season` TEXT, `Term` TEXT, `MWh` REAL, `GJ` REAL);
INSERT INTO `Heat Storage volume`(`Case`, Year, Country,Region,Area,Season,Term,MWh,GJ) SELECT VHSTOVOLT.VHSTOVOLT AS `Case`, VHSTOVOLT.VHSTOVOLT2 AS Year, ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, VHSTOVOLT.VHSTOVOLT3 AS Area, VHSTOVOLT.VHSTOVOLT4 AS Season, VHSTOVOLT.VHSTOVOLT5 AS Term, Sum(VHSTOVOLT.level) AS MWh, Sum(VHSTOVOLT.level)*3.6 AS GJ FROM ICA INNER JOIN (RRRAAA INNER JOIN VHSTOVOLT ON (RRRAAA.RRRAAA4 = VHSTOVOLT.VHSTOVOLT3) AND (RRRAAA.RRRAAA = VHSTOVOLT.VHSTOVOLT)) ON (ICA.ICA4 = VHSTOVOLT.VHSTOVOLT3) AND (ICA.ICA = VHSTOVOLT.VHSTOVOLT)GROUP BY VHSTOVOLT.VHSTOVOLT, VHSTOVOLT.VHSTOVOLT2, VHSTOVOLT.VHSTOVOLT3, ICA.ICA3, RRRAAA.RRRAAA3, VHSTOVOLT.VHSTOVOLT4, VHSTOVOLT.VHSTOVOLT5;
DROP TABLE if exists `Weekly Hydro Storage Volume`;
CREATE TABLE `Weekly Hydro Storage Volume`(`Case` TEXT, `Year`TEXT, `Country` TEXT, `Region` TEXT, `Area` TEXT,`Week` TEXT, `MWh` REAL);
INSERT INTO `Weekly Hydro Storage Volume`(`Case`,Year,Country, Region,Area,Week,MWh) SELECT VHYRS_S.VHYRS_S AS [Case], VHYRS_S.VHYRS_S2 AS [Year], ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, VHYRS_S.VHYRS_S3 AS Area, VHYRS_S.VHYRS_S4 AS Week, VHYRS_S.level AS MWh FROM (VHYRS_S INNER JOIN ICA ON VHYRS_S.VHYRS_S3 = ICA.ICA4) INNER JOIN RRRAAA ON VHYRS_S.VHYRS_S3 = RRRAAA.RRRAAA4;
DROP TABLE if exists `Exogenous Generation Technology - MW`;
CREATE TABLE `Exogenous Generation Technology - MW`(`Case` TEXT,`Year` TEXT,`Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT, `Technology` TEXT, `MW` TEXT);
INSERT INTO `Exogenous Generation Technology - MW`(`Case` ,Year,Country,Region,Area,Fuel,Technology,MW) SELECT GKFX.GKFX AS [`Case`], GKFX.GKFX3 AS [Year], ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, GKFX.GKFX4 AS Area, IGF.IGF4 AS Fuel, GKFX.GKFX5 AS Technology, Sum(GKFX.value) AS MW FROM IGF INNER JOIN (RRRAAA INNER JOIN (ICA INNER JOIN (Y INNER JOIN GKFX ON (Y.Y3 = GKFX.GKFX3) AND (Y.Y = GKFX.GKFX)) ON (ICA.ICA4 = GKFX.GKFX4) AND (ICA.ICA = GKFX.GKFX)) ON (RRRAAA.RRRAAA4 = GKFX.GKFX4) AND (RRRAAA.RRRAAA = GKFX.GKFX)) ON (IGF.IGF = GKFX.GKFX) AND (IGF.IGF3 = GKFX.GKFX5) GROUP BY GKFX.GKFX, GKFX.GKFX3, ICA.ICA3, RRRAAA.RRRAAA3, GKFX.GKFX4, IGF.IGF4, GKFX.GKFX5;
DROP TABLE if exists `Exogenous Electricity Generation Technology - MW`;
CREATE TABLE `Exogenous Electricity Generation Technology - MW`(`Case` TEXT,`Year` TEXT,`Country` TEXT, `Region` TEXT, `Area` TEXT, `Fuel` TEXT, `Technology` TEXT, `MW` TEXT);
INSERT INTO `Exogenous Electricity Generation Technology - MW`(`Case`,Year,Country,Region,Area,Fuel,Technology,MW) SELECT [Exogenous Generation Technology - MW].`Case`, [Exogenous Generation Technology - MW].Year, [Exogenous Generation Technology - MW].Country AS Country, [Exogenous Generation Technology - MW].Region, [Exogenous Generation Technology - MW].Area, [Exogenous Generation Technology - MW].Fuel, [Exogenous Generation Technology - MW].Technology, [Exogenous Generation Technology - MW].MW FROM ([Exogenous Generation Technology - MW] INNER JOIN IGE ON ([Exogenous Generation Technology - MW].`Case` = IGE.IGE) AND ([Exogenous Generation Technology - MW].Technology = IGE.IGE3)) INNER JOIN GDATA ON (IGE.IGE3 = GDATA.GDATA3) AND (IGE.IGE = GDATA.GDATA) WHERE (((GDATA.value)<>5) AND ((GDATA.GDATA4)="GDTYPE"));
DROP TABLE if exists `Electricity Demand - Term`;
CREATE TABLE `Electricity Demand - Term`(`Case` TEXT, Year TEXT, Country TEXT, Region TEXT, Season TEXT, Term TEXT, MW REAL, MWh REAL);
INSERT INTO `Electricity Demand - Term`(`Case`,Year,Country,Region,Season,Term,MW,MWh) SELECT DE.DE AS [Case], DE.DE3 AS [Year], CCCRRR.CCCRRR3 AS Country, DE.DE4 AS Region, DE_VAR_T.DE_VAR_T4 AS Season, DE_VAR_T.DE_VAR_T5 AS Timestep, Sum([DE].[value]*[DE_VAR_T].[value]/[IDE_SUMST].[value]) AS MW, Sum([DE].[value]*[DE_VAR_T].[value]/[IDE_SUMST].[value]*[IHOURSINST].[value]) AS MWh FROM IHOURSINST INNER JOIN (Y INNER JOIN ((DE_VAR_T INNER JOIN (DE INNER JOIN CCCRRR ON (DE.DE = CCCRRR.CCCRRR) AND (DE.DE4 = CCCRRR.CCCRRR4)) ON (DE_VAR_T.DE_VAR_T = DE.DE) AND (DE_VAR_T.DE_VAR_T3 = DE.DE4)) INNER JOIN IDE_SUMST ON (DE.DE = IDE_SUMST.IDE_SUMST) AND (DE_VAR_T.DE_VAR_T3 = IDE_SUMST.IDE_SUMST3)) ON (Y.Y = DE.DE) AND (Y.Y3 = DE.DE3)) ON (DE_VAR_T.DE_VAR_T5 = IHOURSINST.IHOURSINST4) AND (DE_VAR_T.DE_VAR_T4 = IHOURSINST.IHOURSINST3) AND (IHOURSINST.IHOURSINST = DE_VAR_T.DE_VAR_T) GROUP BY DE.DE, DE.DE3, CCCRRR.CCCRRR3, DE.DE4, DE_VAR_T.DE_VAR_T4, DE_VAR_T.DE_VAR_T5;
DROP TABLE if exists `Electricity Demand - Annual`;
CREATE TABLE `Electricity Demand - Annual`(`Case` TEXT, Year TEXT, Country TEXT, Region TEXT, TWh REAL, GWh REAL);
INSERT INTO `Electricity Demand - Annual`(`Case` , Year , Country , Region, TWh , GWh) SELECT [Electricity demand - Term].`Case`, [Electricity demand - Term].Year, [Electricity demand - Term].Country, [Electricity demand - Term].Region, Sum([MWH])/1000000 AS TWh, Sum([MWH])/1000 AS GWh FROM [Electricity demand - Term] GROUP BY [Electricity demand - Term].`Case`, [Electricity demand - Term].Year, [Electricity demand - Term].Country, [Electricity demand - Term].Region;
DROP TABLE if exists `Heat Demand - Term`;
CREATE TABLE `Heat Demand - Term`(`Case` TEXT, Year TEXT, Country TEXT, Region TEXT, Area TEXT,Season TEXT, Term TEXT, MW REAL, MWh REAL, GJ REAL);
INSERT INTO `Heat Demand - Term`(`Case`,Year,Country,Region,Area,Season,Term,MW,MWh,GJ) SELECT DH.DH AS [Case], DH.DH3 AS [Year], ICA.ICA3 AS Country, RRRAAA.RRRAAA3 AS Region, DH.DH4 AS Area, DH_VAR_T.DH_VAR_T4 AS Season, DH_VAR_T.DH_VAR_T5 AS Term, Sum([DH].[value]*[DH_VAR_T].[value]/[IDH_SUMST].[value]) AS MW, Sum([DH].[value]*[DH_VAR_T].[value]/[IDH_SUMST].[value]*[IHOURSINST].[value]) AS MWh, Sum([DH].[value]*[DH_VAR_T].[value]/[IDH_SUMST].[value]*[IHOURSINST].[value])*3.6 AS GJ FROM IHOURSINST INNER JOIN ((C INNER JOIN (ICA INNER JOIN (Y INNER JOIN (DH INNER JOIN (IDH_SUMST INNER JOIN DH_VAR_T ON IDH_SUMST.IDH_SUMST3 = DH_VAR_T.DH_VAR_T3) ON (DH.DH = IDH_SUMST.IDH_SUMST) AND (DH.DH = DH_VAR_T.DH_VAR_T) AND (DH.DH4 = DH_VAR_T.DH_VAR_T3)) ON (Y.Y = DH.DH) AND (Y.Y3 = DH.DH3)) ON (ICA.ICA = DH.DH) AND (ICA.ICA4 = DH.DH4)) ON (C.C = DH.DH) AND (C.C3 = ICA.ICA3)) INNER JOIN RRRAAA ON (DH.DH4 = RRRAAA.RRRAAA4) AND (DH.DH = RRRAAA.RRRAAA)) ON (DH_VAR_T.DH_VAR_T5 = IHOURSINST.IHOURSINST4) AND (DH_VAR_T.DH_VAR_T4 = IHOURSINST.IHOURSINST3) AND (IHOURSINST.IHOURSINST = DH_VAR_T.DH_VAR_T) GROUP BY DH.DH, DH.DH3, ICA.ICA3, RRRAAA.RRRAAA3, DH.DH4, DH_VAR_T.DH_VAR_T4, DH_VAR_T.DH_VAR_T;
DROP TABLE if exists `Heat Demand - Annual`;
CREATE TABLE `Heat Demand - Annual`(`Case` TEXT, Year TEXT, Country TEXT, Region TEXT, Area TEXT, TWh REAL, PJ REAL);
INSERT INTO `Heat Demand - Annual`(`Case`, Year, Country, Region,Area,TWh,PJ) SELECT [Heat demand - Term].`Case`, [Heat demand - Term].Year, [Heat demand - Term].Country, [Heat demand - Term].Region, [Heat demand - Term].Area, Sum([MWh]/1000000) AS TWh, Sum([MWh]/1000000)*3.6 AS PJ FROM [Heat demand - Term] GROUP BY [Heat demand - Term].`Case`, [Heat demand - Term].Year, [Heat demand - Term].Country, [Heat demand - Term].Region, [Heat demand - Term].Area;
DROP TABLE if exists `El Transmission between regions net - Term`;
CREATE TABLE `El Transmission between regions net - Term`(`Case` TEXT, Year TEXT, Source TEXT, Destination TEXT, Season TEXT, Term TEXT, GW REAL);
INSERT INTO `El Transmission between regions net - Term`(`Case` , Year, Source, Destination, Season, Term, GW) SELECT VX_T.VX_T AS [Case], VX_T.VX_T2 AS [Year], VX_T.VX_T3 AS Source, VX_T.VX_T4 AS Destination, VX_T.VX_T5 AS Season, VX_T.VX_T6 AS [Time], Sum(VX_T.level-VX_T_1.level)/1000 AS GW FROM VX_T INNER JOIN VX_T AS VX_T_1 ON (VX_T.VX_T6 = VX_T_1.VX_T6) AND (VX_T.VX_T5 = VX_T_1.VX_T5) AND (VX_T.VX_T4 = VX_T_1.VX_T3) AND (VX_T.VX_T3 = VX_T_1.VX_T4) AND (VX_T.VX_T2 = VX_T_1.VX_T2) AND (VX_T.VX_T = VX_T_1.VX_T) GROUP BY VX_T.VX_T, VX_T.VX_T2, VX_T.VX_T3, VX_T.VX_T4, VX_T.VX_T5, VX_T.VX_T6;
DROP TABLE if exists `El Transmission between Countries`;
CREATE TABLE `El Transmission between Countries`(`Case` TEXT, Year TEXT,Country1 TEXT, Region1 TEXT,Country2 TEXT,Region2 TEXT,GWh REAL);
INSERT INTO `El Transmission between Countries`(`Case`,Year,Country1,Region1,Country2,Region2,GWh) SELECT VX_T.VX_T AS [`Case`], VX_T.VX_T2 AS [Year], CCCRRR.CCCRRR3 AS Country1, VX_T.VX_T3 AS Region1, CCCRRR_1.CCCRRR3 AS Country2, VX_T.VX_T4 AS Region2, Sum(VX_T.level*IHOURSINST.value)/1000 AS GWh FROM CCCRRR INNER JOIN (CCCRRR AS CCCRRR_1 INNER JOIN (VX_T INNER JOIN IHOURSINST ON (VX_T.VX_T6 = IHOURSINST.IHOURSINST4) AND (VX_T.VX_T5 = IHOURSINST.IHOURSINST3) AND (VX_T.VX_T = IHOURSINST.IHOURSINST)) ON (CCCRRR_1.CCCRRR4 = VX_T.VX_T4) AND (CCCRRR_1.CCCRRR = VX_T.VX_T)) ON (CCCRRR.CCCRRR4 = VX_T.VX_T3) AND (CCCRRR.CCCRRR = VX_T.VX_T) GROUP BY VX_T.VX_T, VX_T.VX_T2, CCCRRR.CCCRRR3, VX_T.VX_T3, CCCRRR_1.CCCRRR3, VX_T.VX_T4;