CREATE TABLE `finance_financialstatement$tag_3` (
  `id` bigint(20) NOT NULL,
  `tagname` varchar(200) DEFAULT NULL,
  `taggroup` varchar(200) DEFAULT NULL,
  `tagdescription` varchar(200) DEFAULT NULL,
  `tagid` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


CREATE TABLE `finance_financialstatement$tag_2` (
  `id` bigint(20) NOT NULL,
  `tagname` varchar(200) DEFAULT NULL,
  `taggroup` varchar(200) DEFAULT NULL,
  `tagdescription` varchar(200) DEFAULT NULL,
  `tagid` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


CREATE TABLE `finance_financialstatement$tag` (
  `id` bigint(20) NOT NULL,
  `tagname` varchar(200) DEFAULT NULL,
  `taggroup` varchar(200) DEFAULT NULL,
  `tagdescription` varchar(200) DEFAULT NULL,
  `tagid` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `finance_financialstatement$customeritem` (
  `id` bigint(20) NOT NULL,
  `item` varchar(200) DEFAULT NULL,
  `customer` varchar(200) DEFAULT NULL,
  `alias` varchar(200) DEFAULT NULL,
  `tag` varchar(200) DEFAULT NULL,
  `tag3` varchar(200) DEFAULT NULL,
  `source` varchar(200) DEFAULT NULL,
  `tag2` varchar(200) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  `category` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `finance_financialstatement$fs` (
  `id` bigint(20) NOT NULL,
  `category` varchar(200) DEFAULT NULL,
  `customer` varchar(200) DEFAULT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  `item` varchar(200) DEFAULT NULL,
  `changeddate` datetime DEFAULT NULL,
  `v_2021` decimal(28,8) DEFAULT NULL,
  `v_2022` decimal(28,8) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `v_2020` decimal(28,8) DEFAULT NULL,
  `v_2025` decimal(28,8) DEFAULT NULL,
  `v_2026` decimal(28,8) DEFAULT NULL,
  `v_2023` decimal(28,8) DEFAULT NULL,
  `v_2024` decimal(28,8) DEFAULT NULL,
  `v_2029` decimal(28,8) DEFAULT NULL,
  `v_2027` decimal(28,8) DEFAULT NULL,
  `v_2028` decimal(28,8) DEFAULT NULL,
  `deleteflag` varchar(1) DEFAULT NULL,
  `createddate` datetime DEFAULT NULL,
  `v_2030` decimal(28,8) DEFAULT NULL,
  `value` decimal(28,8) DEFAULT NULL,
  `v_2014` decimal(28,8) DEFAULT NULL,
  `v_2015` decimal(28,8) DEFAULT NULL,
  `v_2013` decimal(28,8) DEFAULT NULL,
  `v_2018` decimal(28,8) DEFAULT NULL,
  `v_2019` decimal(28,8) DEFAULT NULL,
  `v_2016` decimal(28,8) DEFAULT NULL,
  `v_2017` decimal(28,8) DEFAULT NULL,
  `system$changedby` bigint(20) DEFAULT NULL,
  `system$owner` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_finance_financialstatement$fs_system$changedby` (`system$changedby`,`id`),
  KEY `idx_finance_financialstatement$fs_system$owner` (`system$owner`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

create view fs_tagged as select `d`.`CUSTOMER` AS `CUSTOMER`,`d`.`CATEGORY` AS `CATEGORY`,`d`.`ITEM` AS `ITEM`,`d`.`UNIT` AS `UNIT`,`d`.`CURRENCY` AS `CURRENCY`,`d`.`REMARK` AS `REMARK`,`d`.`V_2013` AS `V_2013`,`d`.`V_2014` AS `V_2014`,`d`.`V_2015` AS `V_2015`,`d`.`V_2016` AS `V_2016`,`d`.`V_2017` AS `V_2017`,`d`.`V_2018` AS `V_2018`,`d`.`V_2019` AS `V_2019`,`d`.`V_2020` AS `V_2020`,`d`.`V_2021` AS `V_2021`,`d`.`V_2022` AS `V_2022`,`d`.`V_2023` AS `V_2023`,`d`.`V_2024` AS `V_2024`,`d`.`V_2025` AS `V_2025`,`d`.`V_2026` AS `V_2026`,`d`.`V_2027` AS `V_2027`,`d`.`V_2028` AS `V_2028`,`d`.`V_2029` AS `V_2029`,`d`.`V_2030` AS `V_2030`,`e`.`TAGID1` AS `TAGID1`,`e`.`TAGID2` AS `TAGID2`,`e`.`TAGID3` AS `TAGID3`,`e`.`TAGNAME1` AS `TAGNAME1`,`e`.`TAGNAME2` AS `TAGNAME2`,`e`.`TAGNAME3` AS `TAGNAME3`,`e`.`TAG1_EN_NAME` AS `TAG1_EN_NAME`,`e`.`TAG2_EN_NAME` AS `TAG2_EN_NAME`,`e`.`TAG3_EN_NAME` AS `TAG3_EN_NAME` from (((select `test`.`finance_financialstatement$fs`.`customer` AS `CUSTOMER`,`test`.`finance_financialstatement$fs`.`category` AS `CATEGORY`,`test`.`finance_financialstatement$fs`.`item` AS `ITEM`,`test`.`finance_financialstatement$fs`.`unit` AS `UNIT`,`test`.`finance_financialstatement$fs`.`currency` AS `CURRENCY`,`test`.`finance_financialstatement$fs`.`remark` AS `REMARK`,`test`.`finance_financialstatement$fs`.`v_2013` AS `V_2013`,`test`.`finance_financialstatement$fs`.`v_2014` AS `V_2014`,`test`.`finance_financialstatement$fs`.`v_2015` AS `V_2015`,`test`.`finance_financialstatement$fs`.`v_2016` AS `V_2016`,`test`.`finance_financialstatement$fs`.`v_2017` AS `V_2017`,`test`.`finance_financialstatement$fs`.`v_2018` AS `V_2018`,`test`.`finance_financialstatement$fs`.`v_2019` AS `V_2019`,`test`.`finance_financialstatement$fs`.`v_2020` AS `V_2020`,`test`.`finance_financialstatement$fs`.`v_2021` AS `V_2021`,`test`.`finance_financialstatement$fs`.`v_2022` AS `V_2022`,`test`.`finance_financialstatement$fs`.`v_2023` AS `V_2023`,`test`.`finance_financialstatement$fs`.`v_2024` AS `V_2024`,`test`.`finance_financialstatement$fs`.`v_2025` AS `V_2025`,`test`.`finance_financialstatement$fs`.`v_2026` AS `V_2026`,`test`.`finance_financialstatement$fs`.`v_2027` AS `V_2027`,`test`.`finance_financialstatement$fs`.`v_2028` AS `V_2028`,`test`.`finance_financialstatement$fs`.`v_2029` AS `V_2029`,`test`.`finance_financialstatement$fs`.`v_2030` AS `V_2030` from `test`.`finance_financialstatement$fs` where (`test`.`finance_financialstatement$fs`.`deleteflag` = 'N'))) `d` left join (select `a`.`item` AS `ITEM`,`a`.`customer` AS `CUSTOMER`,`a`.`tag` AS `TAGID1`,`a`.`tag2` AS `TAGID2`,`a`.`tag3` AS `TAGID3`,`b`.`tagname` AS `TAGNAME1`,`b`.`tagdescription` AS `TAG1_EN_NAME`,`c1`.`tagname` AS `TAGNAME2`,`c1`.`tagdescription` AS `TAG2_EN_NAME`,`c2`.`tagname` AS `TAGNAME3`,`c2`.`tagdescription` AS `TAG3_EN_NAME` from (((`test`.`finance_financialstatement$customeritem` `a` left join `test`.`finance_financialstatement$tag` `b` on((`a`.`tag` = `b`.`tagid`))) left join `test`.`finance_financialstatement$tag_2` `c1` on((`a`.`tag2` = `c1`.`tagid`))) left join `test`.`finance_financialstatement$tag_3` `c2` on((`a`.`tag3` = `c2`.`tagid`)))) `e` on(((`d`.`CUSTOMER` = `e`.`CUSTOMER`) and (`d`.`ITEM` = `e`.`ITEM`))));


create view fs_sum as select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,`fs_tagged`.`CATEGORY` AS `CATEGORY`,`fs_tagged`.`ITEM` AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,`fs_tagged`.`REMARK` AS `REMARK`,`fs_tagged`.`V_2013` AS `V_2013`,`fs_tagged`.`V_2014` AS `V_2014`,`fs_tagged`.`V_2015` AS `V_2015`,`fs_tagged`.`V_2016` AS `V_2016`,`fs_tagged`.`V_2017` AS `V_2017`,`fs_tagged`.`V_2018` AS `V_2018`,`fs_tagged`.`V_2019` AS `V_2019`,`fs_tagged`.`V_2020` AS `V_2020`,`fs_tagged`.`V_2021` AS `V_2021`,`fs_tagged`.`V_2022` AS `V_2022`,`fs_tagged`.`V_2023` AS `V_2023`,`fs_tagged`.`V_2024` AS `V_2024`,`fs_tagged`.`V_2025` AS `V_2025`,`fs_tagged`.`V_2026` AS `V_2026`,`fs_tagged`.`V_2027` AS `V_2027`,`fs_tagged`.`V_2028` AS `V_2028`,`fs_tagged`.`V_2029` AS `V_2029`,`fs_tagged`.`V_2030` AS `V_2030`,`fs_tagged`.`TAGID1` AS `TAGID`,`fs_tagged`.`TAG1_EN_NAME` AS `TAG_NAME`,'REPORT_ITEM' AS `DTYPE` from `test`.`fs_tagged` where (`fs_tagged`.`TAGID1` is not null) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,`fs_tagged`.`CATEGORY` AS `CATEGORY`,`fs_tagged`.`ITEM` AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,`fs_tagged`.`REMARK` AS `REMARK`,`fs_tagged`.`V_2013` AS `V_2013`,`fs_tagged`.`V_2014` AS `V_2014`,`fs_tagged`.`V_2015` AS `V_2015`,`fs_tagged`.`V_2016` AS `V_2016`,`fs_tagged`.`V_2017` AS `V_2017`,`fs_tagged`.`V_2018` AS `V_2018`,`fs_tagged`.`V_2019` AS `V_2019`,`fs_tagged`.`V_2020` AS `V_2020`,`fs_tagged`.`V_2021` AS `V_2021`,`fs_tagged`.`V_2022` AS `V_2022`,`fs_tagged`.`V_2023` AS `V_2023`,`fs_tagged`.`V_2024` AS `V_2024`,`fs_tagged`.`V_2025` AS `V_2025`,`fs_tagged`.`V_2026` AS `V_2026`,`fs_tagged`.`V_2027` AS `V_2027`,`fs_tagged`.`V_2028` AS `V_2028`,`fs_tagged`.`V_2029` AS `V_2029`,`fs_tagged`.`V_2030` AS `V_2030`,`fs_tagged`.`TAGID2` AS `TAGID`,`fs_tagged`.`TAG2_EN_NAME` AS `TAG_NAME`,'REPORT_ITEM' AS `DTYPE` from `test`.`fs_tagged` where (`fs_tagged`.`TAGID2` is not null)) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,`fs_tagged`.`CATEGORY` AS `CATEGORY`,`fs_tagged`.`ITEM` AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,`fs_tagged`.`REMARK` AS `REMARK`,`fs_tagged`.`V_2013` AS `V_2013`,`fs_tagged`.`V_2014` AS `V_2014`,`fs_tagged`.`V_2015` AS `V_2015`,`fs_tagged`.`V_2016` AS `V_2016`,`fs_tagged`.`V_2017` AS `V_2017`,`fs_tagged`.`V_2018` AS `V_2018`,`fs_tagged`.`V_2019` AS `V_2019`,`fs_tagged`.`V_2020` AS `V_2020`,`fs_tagged`.`V_2021` AS `V_2021`,`fs_tagged`.`V_2022` AS `V_2022`,`fs_tagged`.`V_2023` AS `V_2023`,`fs_tagged`.`V_2024` AS `V_2024`,`fs_tagged`.`V_2025` AS `V_2025`,`fs_tagged`.`V_2026` AS `V_2026`,`fs_tagged`.`V_2027` AS `V_2027`,`fs_tagged`.`V_2028` AS `V_2028`,`fs_tagged`.`V_2029` AS `V_2029`,`fs_tagged`.`V_2030` AS `V_2030`,`fs_tagged`.`TAGID3` AS `TAGID`,`fs_tagged`.`TAG3_EN_NAME` AS `TAG_NAME`,'REPORT_ITEM' AS `DTYPE` from `test`.`fs_tagged` where (`fs_tagged`.`TAGID3` is not null)) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,`fs_tagged`.`TAG1_EN_NAME` AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum(`fs_tagged`.`V_2013`) AS `SUM(V_2013)`,sum(`fs_tagged`.`V_2014`) AS `SUM(V_2014)`,sum(`fs_tagged`.`V_2015`) AS `SUM(V_2015)`,sum(`fs_tagged`.`V_2016`) AS `SUM(V_2016)`,sum(`fs_tagged`.`V_2017`) AS `SUM(V_2017)`,sum(`fs_tagged`.`V_2018`) AS `SUM(V_2018)`,sum(`fs_tagged`.`V_2019`) AS `SUM(V_2019)`,sum(`fs_tagged`.`V_2020`) AS `SUM(V_2020)`,sum(`fs_tagged`.`V_2021`) AS `SUM(V_2021)`,sum(`fs_tagged`.`V_2022`) AS `SUM(V_2022)`,sum(`fs_tagged`.`V_2023`) AS `SUM(V_2023)`,sum(`fs_tagged`.`V_2024`) AS `SUM(V_2024)`,sum(`fs_tagged`.`V_2025`) AS `SUM(V_2025)`,sum(`fs_tagged`.`V_2026`) AS `SUM(V_2026)`,sum(`fs_tagged`.`V_2027`) AS `SUM(V_2027)`,sum(`fs_tagged`.`V_2028`) AS `SUM(V_2028)`,sum(`fs_tagged`.`V_2029`) AS `SUM(V_2029)`,sum(`fs_tagged`.`V_2030`) AS `SUM(V_2030)`,`fs_tagged`.`TAGID1` AS `TAGID`,`fs_tagged`.`TAG1_EN_NAME` AS `TAGNAME`,'TAG_ITEM' AS `DTYPE` from `test`.`fs_tagged` where (`fs_tagged`.`TAGID1` is not null) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`,`fs_tagged`.`TAGID1`,`fs_tagged`.`TAG1_EN_NAME`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,`fs_tagged`.`TAG2_EN_NAME` AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum(`fs_tagged`.`V_2013`) AS `SUM(V_2013)`,sum(`fs_tagged`.`V_2014`) AS `SUM(V_2014)`,sum(`fs_tagged`.`V_2015`) AS `SUM(V_2015)`,sum(`fs_tagged`.`V_2016`) AS `SUM(V_2016)`,sum(`fs_tagged`.`V_2017`) AS `SUM(V_2017)`,sum(`fs_tagged`.`V_2018`) AS `SUM(V_2018)`,sum(`fs_tagged`.`V_2019`) AS `SUM(V_2019)`,sum(`fs_tagged`.`V_2020`) AS `SUM(V_2020)`,sum(`fs_tagged`.`V_2021`) AS `SUM(V_2021)`,sum(`fs_tagged`.`V_2022`) AS `SUM(V_2022)`,sum(`fs_tagged`.`V_2023`) AS `SUM(V_2023)`,sum(`fs_tagged`.`V_2024`) AS `SUM(V_2024)`,sum(`fs_tagged`.`V_2025`) AS `SUM(V_2025)`,sum(`fs_tagged`.`V_2026`) AS `SUM(V_2026)`,sum(`fs_tagged`.`V_2027`) AS `SUM(V_2027)`,sum(`fs_tagged`.`V_2028`) AS `SUM(V_2028)`,sum(`fs_tagged`.`V_2029`) AS `SUM(V_2029)`,sum(`fs_tagged`.`V_2030`) AS `SUM(V_2030)`,`fs_tagged`.`TAGID2` AS `TAGID`,`fs_tagged`.`TAG2_EN_NAME` AS `TAGNAME`,'TAG_ITEM' AS `DTYPE` from `test`.`fs_tagged` where (`fs_tagged`.`TAGID2` is not null) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`,`fs_tagged`.`TAGID2`,`fs_tagged`.`TAG2_EN_NAME`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,`fs_tagged`.`TAG3_EN_NAME` AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum(`fs_tagged`.`V_2013`) AS `SUM(V_2013)`,sum(`fs_tagged`.`V_2014`) AS `SUM(V_2014)`,sum(`fs_tagged`.`V_2015`) AS `SUM(V_2015)`,sum(`fs_tagged`.`V_2016`) AS `SUM(V_2016)`,sum(`fs_tagged`.`V_2017`) AS `SUM(V_2017)`,sum(`fs_tagged`.`V_2018`) AS `SUM(V_2018)`,sum(`fs_tagged`.`V_2019`) AS `SUM(V_2019)`,sum(`fs_tagged`.`V_2020`) AS `SUM(V_2020)`,sum(`fs_tagged`.`V_2021`) AS `SUM(V_2021)`,sum(`fs_tagged`.`V_2022`) AS `SUM(V_2022)`,sum(`fs_tagged`.`V_2023`) AS `SUM(V_2023)`,sum(`fs_tagged`.`V_2024`) AS `SUM(V_2024)`,sum(`fs_tagged`.`V_2025`) AS `SUM(V_2025)`,sum(`fs_tagged`.`V_2026`) AS `SUM(V_2026)`,sum(`fs_tagged`.`V_2027`) AS `SUM(V_2027)`,sum(`fs_tagged`.`V_2028`) AS `SUM(V_2028)`,sum(`fs_tagged`.`V_2029`) AS `SUM(V_2029)`,sum(`fs_tagged`.`V_2030`) AS `SUM(V_2030)`,`fs_tagged`.`TAGID3` AS `TAGID`,`fs_tagged`.`TAG3_EN_NAME` AS `TAGNAME`,'TAG_ITEM' AS `DTYPE` from `test`.`fs_tagged` where (`fs_tagged`.`TAGID3` is not null) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`,`fs_tagged`.`TAGID3`,`fs_tagged`.`TAG3_EN_NAME`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'Total Revenue' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum(`fs_tagged`.`V_2013`) AS `SUM(V_2013)`,sum(`fs_tagged`.`V_2014`) AS `SUM(V_2014)`,sum(`fs_tagged`.`V_2015`) AS `SUM(V_2015)`,sum(`fs_tagged`.`V_2016`) AS `SUM(V_2016)`,sum(`fs_tagged`.`V_2017`) AS `SUM(V_2017)`,sum(`fs_tagged`.`V_2018`) AS `SUM(V_2018)`,sum(`fs_tagged`.`V_2019`) AS `SUM(V_2019)`,sum(`fs_tagged`.`V_2020`) AS `SUM(V_2020)`,sum(`fs_tagged`.`V_2021`) AS `SUM(V_2021)`,sum(`fs_tagged`.`V_2022`) AS `SUM(V_2022)`,sum(`fs_tagged`.`V_2023`) AS `SUM(V_2023)`,sum(`fs_tagged`.`V_2024`) AS `SUM(V_2024)`,sum(`fs_tagged`.`V_2025`) AS `SUM(V_2025)`,sum(`fs_tagged`.`V_2026`) AS `SUM(V_2026)`,sum(`fs_tagged`.`V_2027`) AS `SUM(V_2027)`,sum(`fs_tagged`.`V_2028`) AS `SUM(V_2028)`,sum(`fs_tagged`.`V_2029`) AS `SUM(V_2029)`,sum(`fs_tagged`.`V_2030`) AS `SUM(V_2030)`,'NA' AS `TAGID`,'Total Revenue' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0005') or (`fs_tagged`.`TAGID1` = 'T0001')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'Total Gross Revenue （Including JV & Associate)' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum(`fs_tagged`.`V_2013`) AS `SUM(V_2013)`,sum(`fs_tagged`.`V_2014`) AS `SUM(V_2014)`,sum(`fs_tagged`.`V_2015`) AS `SUM(V_2015)`,sum(`fs_tagged`.`V_2016`) AS `SUM(V_2016)`,sum(`fs_tagged`.`V_2017`) AS `SUM(V_2017)`,sum(`fs_tagged`.`V_2018`) AS `SUM(V_2018)`,sum(`fs_tagged`.`V_2019`) AS `SUM(V_2019)`,sum(`fs_tagged`.`V_2020`) AS `SUM(V_2020)`,sum(`fs_tagged`.`V_2021`) AS `SUM(V_2021)`,sum(`fs_tagged`.`V_2022`) AS `SUM(V_2022)`,sum(`fs_tagged`.`V_2023`) AS `SUM(V_2023)`,sum(`fs_tagged`.`V_2024`) AS `SUM(V_2024)`,sum(`fs_tagged`.`V_2025`) AS `SUM(V_2025)`,sum(`fs_tagged`.`V_2026`) AS `SUM(V_2026)`,sum(`fs_tagged`.`V_2027`) AS `SUM(V_2027)`,sum(`fs_tagged`.`V_2028`) AS `SUM(V_2028)`,sum(`fs_tagged`.`V_2029`) AS `SUM(V_2029)`,sum(`fs_tagged`.`V_2030`) AS `SUM(V_2030)`,'NA' AS `TAGID`,'Total Gross Revenue' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0005') or (`fs_tagged`.`TAGID1` = 'T0001') or (`fs_tagged`.`TAGID1` = 'T0012')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'Additional PPE & Intangible' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum(`fs_tagged`.`V_2013`) AS `SUM(V_2013)`,sum(`fs_tagged`.`V_2014`) AS `SUM(V_2014)`,sum(`fs_tagged`.`V_2015`) AS `SUM(V_2015)`,sum(`fs_tagged`.`V_2016`) AS `SUM(V_2016)`,sum(`fs_tagged`.`V_2017`) AS `SUM(V_2017)`,sum(`fs_tagged`.`V_2018`) AS `SUM(V_2018)`,sum(`fs_tagged`.`V_2019`) AS `SUM(V_2019)`,sum(`fs_tagged`.`V_2020`) AS `SUM(V_2020)`,sum(`fs_tagged`.`V_2021`) AS `SUM(V_2021)`,sum(`fs_tagged`.`V_2022`) AS `SUM(V_2022)`,sum(`fs_tagged`.`V_2023`) AS `SUM(V_2023)`,sum(`fs_tagged`.`V_2024`) AS `SUM(V_2024)`,sum(`fs_tagged`.`V_2025`) AS `SUM(V_2025)`,sum(`fs_tagged`.`V_2026`) AS `SUM(V_2026)`,sum(`fs_tagged`.`V_2027`) AS `SUM(V_2027)`,sum(`fs_tagged`.`V_2028`) AS `SUM(V_2028)`,sum(`fs_tagged`.`V_2029`) AS `SUM(V_2029)`,sum(`fs_tagged`.`V_2030`) AS `SUM(V_2030)`,'NA' AS `TAGID`,'Additional PPE & Intangible' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0017') or (`fs_tagged`.`TAGID1` = 'T0016')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'CAPEX' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,abs(sum(`fs_tagged`.`V_2013`)) AS `SUM(V_2013)`,abs(sum(`fs_tagged`.`V_2014`)) AS `SUM(V_2014)`,abs(sum(`fs_tagged`.`V_2015`)) AS `SUM(V_2015)`,abs(sum(`fs_tagged`.`V_2016`)) AS `SUM(V_2016)`,abs(sum(`fs_tagged`.`V_2017`)) AS `SUM(V_2017)`,abs(sum(`fs_tagged`.`V_2018`)) AS `SUM(V_2018)`,abs(sum(`fs_tagged`.`V_2019`)) AS `SUM(V_2019)`,abs(sum(`fs_tagged`.`V_2020`)) AS `SUM(V_2020)`,abs(sum(`fs_tagged`.`V_2021`)) AS `SUM(V_2021)`,abs(sum(`fs_tagged`.`V_2022`)) AS `SUM(V_2022)`,abs(sum(`fs_tagged`.`V_2023`)) AS `SUM(V_2023)`,abs(sum(`fs_tagged`.`V_2024`)) AS `SUM(V_2024)`,abs(sum(`fs_tagged`.`V_2025`)) AS `SUM(V_2025)`,abs(sum(`fs_tagged`.`V_2026`)) AS `SUM(V_2026)`,abs(sum(`fs_tagged`.`V_2027`)) AS `SUM(V_2027)`,abs(sum(`fs_tagged`.`V_2028`)) AS `SUM(V_2028)`,abs(sum(`fs_tagged`.`V_2029`)) AS `SUM(V_2029)`,abs(sum(`fs_tagged`.`V_2030`)) AS `SUM(V_2030)`,'NA' AS `TAGID`,'CAPEX' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0032') or (`fs_tagged`.`TAGID1` = 'T0033')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'All Other Expenses(Exclude D&A)' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,abs(sum(`fs_tagged`.`V_2013`)) AS `SUM(V_2013)`,abs(sum(`fs_tagged`.`V_2014`)) AS `SUM(V_2014)`,abs(sum(`fs_tagged`.`V_2015`)) AS `SUM(V_2015)`,abs(sum(`fs_tagged`.`V_2016`)) AS `SUM(V_2016)`,abs(sum(`fs_tagged`.`V_2017`)) AS `SUM(V_2017)`,abs(sum(`fs_tagged`.`V_2018`)) AS `SUM(V_2018)`,abs(sum(`fs_tagged`.`V_2019`)) AS `SUM(V_2019)`,abs(sum(`fs_tagged`.`V_2020`)) AS `SUM(V_2020)`,abs(sum(`fs_tagged`.`V_2021`)) AS `SUM(V_2021)`,abs(sum(`fs_tagged`.`V_2022`)) AS `SUM(V_2022)`,abs(sum(`fs_tagged`.`V_2023`)) AS `SUM(V_2023)`,abs(sum(`fs_tagged`.`V_2024`)) AS `SUM(V_2024)`,abs(sum(`fs_tagged`.`V_2025`)) AS `SUM(V_2025)`,abs(sum(`fs_tagged`.`V_2026`)) AS `SUM(V_2026)`,abs(sum(`fs_tagged`.`V_2027`)) AS `SUM(V_2027)`,abs(sum(`fs_tagged`.`V_2028`)) AS `SUM(V_2028)`,abs(sum(`fs_tagged`.`V_2029`)) AS `SUM(V_2029)`,abs(sum(`fs_tagged`.`V_2030`)) AS `SUM(V_2030)`,'NA' AS `TAGID`,'All Other Expenses(Exclude D&A)' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0006') or (`fs_tagged`.`TAGID1` = 'T0007') or (`fs_tagged`.`TAGID1` = 'T0011')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'All Accrual Expenses' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,abs(sum(`fs_tagged`.`V_2013`)) AS `SUM(V_2013)`,abs(sum(`fs_tagged`.`V_2014`)) AS `SUM(V_2014)`,abs(sum(`fs_tagged`.`V_2015`)) AS `SUM(V_2015)`,abs(sum(`fs_tagged`.`V_2016`)) AS `SUM(V_2016)`,abs(sum(`fs_tagged`.`V_2017`)) AS `SUM(V_2017)`,abs(sum(`fs_tagged`.`V_2018`)) AS `SUM(V_2018)`,abs(sum(`fs_tagged`.`V_2019`)) AS `SUM(V_2019)`,abs(sum(`fs_tagged`.`V_2020`)) AS `SUM(V_2020)`,abs(sum(`fs_tagged`.`V_2021`)) AS `SUM(V_2021)`,abs(sum(`fs_tagged`.`V_2022`)) AS `SUM(V_2022)`,abs(sum(`fs_tagged`.`V_2023`)) AS `SUM(V_2023)`,abs(sum(`fs_tagged`.`V_2024`)) AS `SUM(V_2024)`,abs(sum(`fs_tagged`.`V_2025`)) AS `SUM(V_2025)`,abs(sum(`fs_tagged`.`V_2026`)) AS `SUM(V_2026)`,abs(sum(`fs_tagged`.`V_2027`)) AS `SUM(V_2027)`,abs(sum(`fs_tagged`.`V_2028`)) AS `SUM(V_2028)`,abs(sum(`fs_tagged`.`V_2029`)) AS `SUM(V_2029)`,abs(sum(`fs_tagged`.`V_2030`)) AS `SUM(V_2030)`,'NA' AS `TAGID`,'All Accrual Expenses' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0043') or (`fs_tagged`.`TAGID1` = 'T0050')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'Debt' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,abs(sum(`fs_tagged`.`V_2013`)) AS `SUM(V_2013)`,abs(sum(`fs_tagged`.`V_2014`)) AS `SUM(V_2014)`,abs(sum(`fs_tagged`.`V_2015`)) AS `SUM(V_2015)`,abs(sum(`fs_tagged`.`V_2016`)) AS `SUM(V_2016)`,abs(sum(`fs_tagged`.`V_2017`)) AS `SUM(V_2017)`,abs(sum(`fs_tagged`.`V_2018`)) AS `SUM(V_2018)`,abs(sum(`fs_tagged`.`V_2019`)) AS `SUM(V_2019)`,abs(sum(`fs_tagged`.`V_2020`)) AS `SUM(V_2020)`,abs(sum(`fs_tagged`.`V_2021`)) AS `SUM(V_2021)`,abs(sum(`fs_tagged`.`V_2022`)) AS `SUM(V_2022)`,abs(sum(`fs_tagged`.`V_2023`)) AS `SUM(V_2023)`,abs(sum(`fs_tagged`.`V_2024`)) AS `SUM(V_2024)`,abs(sum(`fs_tagged`.`V_2025`)) AS `SUM(V_2025)`,abs(sum(`fs_tagged`.`V_2026`)) AS `SUM(V_2026)`,abs(sum(`fs_tagged`.`V_2027`)) AS `SUM(V_2027)`,abs(sum(`fs_tagged`.`V_2028`)) AS `SUM(V_2028)`,abs(sum(`fs_tagged`.`V_2029`)) AS `SUM(V_2029)`,abs(sum(`fs_tagged`.`V_2030`)) AS `SUM(V_2030)`,'NA' AS `TAGID`,'Debt' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0044') or (`fs_tagged`.`TAGID1` = 'T0045')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'Net Cash' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2013` else (`fs_tagged`.`V_2013` * -(1)) end)) AS `SUM(V_2013)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2014` else (`fs_tagged`.`V_2014` * -(1)) end)) AS `SUM(V_2014)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2015` else (`fs_tagged`.`V_2015` * -(1)) end)) AS `SUM(V_2015)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2016` else (`fs_tagged`.`V_2016` * -(1)) end)) AS `SUM(V_2016)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2017` else (`fs_tagged`.`V_2017` * -(1)) end)) AS `SUM(V_2017)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0019') then `fs_tagged`.`V_2018` else (`fs_tagged`.`V_2018` * -(1)) end)) AS `SUM(V_2018)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2019` else (`fs_tagged`.`V_2019` * -(1)) end)) AS `SUM(V_2019)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2020` else (`fs_tagged`.`V_2020` * -(1)) end)) AS `SUM(V_2020)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2021` else (`fs_tagged`.`V_2021` * -(1)) end)) AS `SUM(V_2021)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2022` else (`fs_tagged`.`V_2022` * -(1)) end)) AS `SUM(V_2022)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2023` else (`fs_tagged`.`V_2023` * -(1)) end)) AS `SUM(V_2023)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2024` else (`fs_tagged`.`V_2024` * -(1)) end)) AS `SUM(V_2024)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2025` else (`fs_tagged`.`V_2025` * -(1)) end)) AS `SUM(V_2025)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2026` else (`fs_tagged`.`V_2026` * -(1)) end)) AS `SUM(V_2026)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2027` else (`fs_tagged`.`V_2027` * -(1)) end)) AS `SUM(V_2027)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2028` else (`fs_tagged`.`V_2028` * -(1)) end)) AS `SUM(V_2028)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2029` else (`fs_tagged`.`V_2029` * -(1)) end)) AS `SUM(V_2029)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0018') then `fs_tagged`.`V_2030` else (`fs_tagged`.`V_2030` * -(1)) end)) AS `SUM(V_2030)`,'NA' AS `TAGID`,'Net Cash' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0018') or (`fs_tagged`.`TAGID1` = 'T0019')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`) union all (select `fs_tagged`.`CUSTOMER` AS `CUSTOMER`,NULL AS `CATEGORY`,'Net Debt' AS `ITEM`,`fs_tagged`.`UNIT` AS `UNIT`,`fs_tagged`.`CURRENCY` AS `CURRENCY`,NULL AS `REMARK`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2013` * -(1)) else `fs_tagged`.`V_2013` end)) AS `SUM(V_2013)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2014` * -(1)) else `fs_tagged`.`V_2014` end)) AS `SUM(V_2014)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2015` * -(1)) else `fs_tagged`.`V_2015` end)) AS `SUM(V_2015)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2016` * -(1)) else `fs_tagged`.`V_2016` end)) AS `SUM(V_2016)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2017` * -(1)) else `fs_tagged`.`V_2017` end)) AS `SUM(V_2017)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2018` * -(1)) else `fs_tagged`.`V_2018` end)) AS `SUM(V_2018)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2019` * -(1)) else `fs_tagged`.`V_2019` end)) AS `SUM(V_2019)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2020` * -(1)) else `fs_tagged`.`V_2020` end)) AS `SUM(V_2020)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2021` * -(1)) else `fs_tagged`.`V_2021` end)) AS `SUM(V_2021)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then `fs_tagged`.`V_2022` else `fs_tagged`.`V_2022` end)) AS `SUM(V_2022)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2023` * -(1)) else `fs_tagged`.`V_2023` end)) AS `SUM(V_2023)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2024` * -(1)) else `fs_tagged`.`V_2024` end)) AS `SUM(V_2024)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2025` * -(1)) else `fs_tagged`.`V_2025` end)) AS `SUM(V_2025)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2026` * -(1)) else `fs_tagged`.`V_2026` end)) AS `SUM(V_2026)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2027` * -(1)) else `fs_tagged`.`V_2027` end)) AS `SUM(V_2027)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2028` * -(1)) else `fs_tagged`.`V_2028` end)) AS `SUM(V_2028)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2029` * -(1)) else `fs_tagged`.`V_2029` end)) AS `SUM(V_2029)`,sum((case when (`fs_tagged`.`TAGID1` = 'T0049') then (`fs_tagged`.`V_2030` * -(1)) else `fs_tagged`.`V_2030` end)) AS `SUM(V_2030)`,'NA' AS `TAGID`,'Net Debt' AS `TAGNAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_tagged` where ((`fs_tagged`.`TAGID1` = 'T0044') or (`fs_tagged`.`TAGID1` = 'T0045') or (`fs_tagged`.`TAGID1` = 'T0049')) group by `fs_tagged`.`CUSTOMER`,`fs_tagged`.`UNIT`,`fs_tagged`.`CURRENCY`);


create view fs_sum_yoy as select `fs_sum`.`CUSTOMER` AS `CUSTOMER`,`fs_sum`.`CATEGORY` AS `CATEGORY`,`fs_sum`.`UNIT` AS `UNIT`,`fs_sum`.`CURRENCY` AS `CURRENCY`,concat(`fs_sum`.`ITEM`,' Growth ') AS `ITEM`,(((`fs_sum`.`V_2030` - `fs_sum`.`V_2029`) / abs(`fs_sum`.`V_2029`)) * 100) AS `V_2030`,(((`fs_sum`.`V_2029` - `fs_sum`.`V_2028`) / abs(`fs_sum`.`V_2028`)) * 100) AS `V_2029`,(((`fs_sum`.`V_2028` - `fs_sum`.`V_2027`) / abs(`fs_sum`.`V_2027`)) * 100) AS `V_2028`,(((`fs_sum`.`V_2027` - `fs_sum`.`V_2026`) / abs(`fs_sum`.`V_2026`)) * 100) AS `V_2027`,(((`fs_sum`.`V_2026` - `fs_sum`.`V_2025`) / abs(`fs_sum`.`V_2025`)) * 100) AS `V_2026`,(((`fs_sum`.`V_2025` - `fs_sum`.`V_2024`) / abs(`fs_sum`.`V_2024`)) * 100) AS `V_2025`,(((`fs_sum`.`V_2024` - `fs_sum`.`V_2023`) / abs(`fs_sum`.`V_2023`)) * 100) AS `V_2024`,(((`fs_sum`.`V_2023` - `fs_sum`.`V_2022`) / abs(`fs_sum`.`V_2022`)) * 100) AS `V_2023`,(((`fs_sum`.`V_2022` - `fs_sum`.`V_2021`) / abs(`fs_sum`.`V_2021`)) * 100) AS `V_2022`,(((`fs_sum`.`V_2021` - `fs_sum`.`V_2020`) / abs(`fs_sum`.`V_2020`)) * 100) AS `V_2021`,(((`fs_sum`.`V_2020` - `fs_sum`.`V_2019`) / abs(`fs_sum`.`V_2019`)) * 100) AS `V_2020`,(((`fs_sum`.`V_2019` - `fs_sum`.`V_2018`) / abs(`fs_sum`.`V_2018`)) * 100) AS `V_2019`,(((`fs_sum`.`V_2018` - `fs_sum`.`V_2017`) / abs(`fs_sum`.`V_2017`)) * 100) AS `V_2018`,(((`fs_sum`.`V_2017` - `fs_sum`.`V_2016`) / abs(`fs_sum`.`V_2016`)) * 100) AS `V_2017`,(((`fs_sum`.`V_2016` - `fs_sum`.`V_2015`) / abs(`fs_sum`.`V_2015`)) * 100) AS `V_2016`,(((`fs_sum`.`V_2015` - `fs_sum`.`V_2014`) / abs(`fs_sum`.`V_2014`)) * 100) AS `V_2015`,(((`fs_sum`.`V_2014` - `fs_sum`.`V_2013`) / abs(`fs_sum`.`V_2013`)) * 100) AS `V_2014`,0 AS `V_2013`,concat(`fs_sum`.`TAGID`,'G') AS `TAGID`,concat(`fs_sum`.`TAG_NAME`,' Growth') AS `TAG_NAME`,'CAL_ITEM' AS `DTYPE` from `test`.`fs_sum` where ((`fs_sum`.`DTYPE` = 'TAG_ITEM') or (`fs_sum`.`DTYPE` = 'CAL_ITEM')) union all select `fs_sum`.`CUSTOMER` AS `CUSTOMER`,`fs_sum`.`CATEGORY` AS `CATEGORY`,`fs_sum`.`UNIT` AS `UNIT`,`fs_sum`.`CURRENCY` AS `CURRENCY`,concat(`fs_sum`.`ITEM`,' Growth') AS `ITEM`,(((`fs_sum`.`V_2030` - `fs_sum`.`V_2028`) / abs(`fs_sum`.`V_2028`)) * 100) AS `V_2030`,(((`fs_sum`.`V_2029` - `fs_sum`.`V_2027`) / abs(`fs_sum`.`V_2027`)) * 100) AS `V_2029`,(((`fs_sum`.`V_2028` - `fs_sum`.`V_2026`) / abs(`fs_sum`.`V_2026`)) * 100) AS `V_2028`,(((`fs_sum`.`V_2027` - `fs_sum`.`V_2025`) / abs(`fs_sum`.`V_2025`)) * 100) AS `V_2027`,(((`fs_sum`.`V_2026` - `fs_sum`.`V_2024`) / abs(`fs_sum`.`V_2024`)) * 100) AS `V_2026`,(((`fs_sum`.`V_2025` - `fs_sum`.`V_2023`) / abs(`fs_sum`.`V_2023`)) * 100) AS `V_2025`,(((`fs_sum`.`V_2024` - `fs_sum`.`V_2022`) / abs(`fs_sum`.`V_2022`)) * 100) AS `V_2024`,(((`fs_sum`.`V_2023` - `fs_sum`.`V_2021`) / abs(`fs_sum`.`V_2021`)) * 100) AS `V_2023`,(((`fs_sum`.`V_2022` - `fs_sum`.`V_2020`) / abs(`fs_sum`.`V_2020`)) * 100) AS `V_2022`,(((`fs_sum`.`V_2021` - `fs_sum`.`V_2019`) / abs(`fs_sum`.`V_2019`)) * 100) AS `V_2021`,(((`fs_sum`.`V_2020` - `fs_sum`.`V_2018`) / abs(`fs_sum`.`V_2018`)) * 100) AS `V_2020`,(((`fs_sum`.`V_2019` - `fs_sum`.`V_2017`) / abs(`fs_sum`.`V_2017`)) * 100) AS `V_2019`,(((`fs_sum`.`V_2018` - `fs_sum`.`V_2016`) / abs(`fs_sum`.`V_2016`)) * 100) AS `V_2018`,(((`fs_sum`.`V_2017` - `fs_sum`.`V_2015`) / abs(`fs_sum`.`V_2015`)) * 100) AS `V_2017`,(((`fs_sum`.`V_2016` - `fs_sum`.`V_2014`) / abs(`fs_sum`.`V_2014`)) * 100) AS `V_2016`,(((`fs_sum`.`V_2015` - `fs_sum`.`V_2013`) / abs(`fs_sum`.`V_2013`)) * 100) AS `V_2015`,0 AS `V_2014`,0 AS `V_2013`,concat(`fs_sum`.`TAGID`,'GT') AS `TAGID`,concat(`fs_sum`.`TAG_NAME`,' Growth Trend') AS `TAG_NAME`,'GT_ITEM' AS `DTYPE` from `test`.`fs_sum` where ((`fs_sum`.`DTYPE` = 'TAG_ITEM') or (`fs_sum`.`DTYPE` = 'CAL_ITEM')) union all select `fs_sum`.`CUSTOMER` AS `CUSTOMER`,`fs_sum`.`CATEGORY` AS `CATEGORY`,`fs_sum`.`UNIT` AS `UNIT`,`fs_sum`.`CURRENCY` AS `CURRENCY`,`fs_sum`.`ITEM` AS `ITEM`,`fs_sum`.`V_2030` AS `V_2030`,`fs_sum`.`V_2029` AS `V_2029`,`fs_sum`.`V_2028` AS `V_2028`,`fs_sum`.`V_2027` AS `V_2027`,`fs_sum`.`V_2026` AS `V_2026`,`fs_sum`.`V_2025` AS `V_2025`,`fs_sum`.`V_2024` AS `V_2024`,`fs_sum`.`V_2023` AS `V_2023`,`fs_sum`.`V_2022` AS `V_2022`,`fs_sum`.`V_2021` AS `V_2021`,`fs_sum`.`V_2020` AS `V_2020`,`fs_sum`.`V_2019` AS `V_2019`,`fs_sum`.`V_2018` AS `V_2018`,`fs_sum`.`V_2017` AS `V_2017`,`fs_sum`.`V_2016` AS `V_2016`,`fs_sum`.`V_2015` AS `V_2015`,`fs_sum`.`V_2014` AS `V_2014`,`fs_sum`.`V_2013` AS `V_2013`,`fs_sum`.`TAGID` AS `TAGID`,`fs_sum`.`TAG_NAME` AS `TAG_NAME`,`fs_sum`.`DTYPE` AS `DTYPE` from `test`.`fs_sum`;


create view fs_pivot as select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2013` AS `VAL`,2013 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2014` AS `VAL`,2014 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2015` AS `VAL`,2015 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2016` AS `VAL`,2016 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2017` AS `VAL`,2017 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2018` AS `VAL`,2018 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2019` AS `VAL`,2019 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` where (`fs_sum_yoy`.`V_2019` is not null) union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2020` AS `VAL`,2020 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` where (`fs_sum_yoy`.`V_2020` is not null) union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2021` AS `VAL`,2021 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` where (`fs_sum_yoy`.`V_2021` is not null) union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2022` AS `VAL`,2022 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` where (`fs_sum_yoy`.`V_2022` is not null) union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2023` AS `VAL`,2023 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` where (`fs_sum_yoy`.`V_2023` is not null) union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2024` AS `VAL`,2024 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` where (`fs_sum_yoy`.`V_2024` is not null) union all select `fs_sum_yoy`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_yoy`.`CATEGORY` AS `CATEGORY`,`fs_sum_yoy`.`UNIT` AS `UNIT`,`fs_sum_yoy`.`CURRENCY` AS `CURRENCY`,`fs_sum_yoy`.`ITEM` AS `ITEM`,`fs_sum_yoy`.`V_2025` AS `VAL`,2025 AS `YYYY`,`fs_sum_yoy`.`TAGID` AS `TAGID`,`fs_sum_yoy`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_yoy`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_yoy` where (`fs_sum_yoy`.`V_2025` is not null);


create view fs_sum_2 as select `fs_pivot`.`CUSTOMER` AS `CUSTOMER`,`fs_pivot`.`CATEGORY` AS `CATEGORY`,`fs_pivot`.`UNIT` AS `UNIT`,`fs_pivot`.`CURRENCY` AS `CURRENCY`,`fs_pivot`.`ITEM` AS `ITEM`,`fs_pivot`.`VAL` AS `VAL`,`fs_pivot`.`YYYY` AS `YYYY`,`fs_pivot`.`TAGID` AS `TAGID`,`fs_pivot`.`TAG_NAME` AS `TAG_NAME`,`fs_pivot`.`DTYPE` AS `DTYPE` from `test`.`fs_pivot` union all (select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`CATEGORY` AS `CATEGORY`,`a`.`UNIT` AS `UNIT`,`a`.`CURRENCY` AS `CURRENCY`,concat(`a`.`TAG_NAME`,'/Operating Revenue') AS `ITEM`,(`a`.`VAL` / `b`.`VAL`) AS `VAL`,`a`.`YYYY` AS `YYYY`,concat(`a`.`TAGID`,'/OpR') AS `TAGID`,'NA' AS `TAG_NAME`,'CAL_ITEM' AS `DTYPE` from (`test`.`fs_pivot` `a` left join `test`.`fs_pivot` `b` on(((`a`.`CUSTOMER` = `b`.`CUSTOMER`) and (`a`.`YYYY` = `b`.`YYYY`) and (`b`.`TAGID` = 'T0001') and (`b`.`DTYPE` = 'TAG_ITEM')))) where (((`a`.`DTYPE` = 'TAG_ITEM') and ((`a`.`TAGID` = 'T0002') or (`a`.`TAGID` = 'T0011') or (`a`.`TAGID` = 'T0010') or (`a`.`TAGID` = 'T0006') or (`a`.`TAGID` = 'T0018') or (`a`.`TAGID` = 'T0007') or (`a`.`TAGID` = 'T0003') or (`a`.`TAGID` = 'T20002'))) or ((`a`.`DTYPE` = 'CAL_ITEM') and ((`a`.`TAG_NAME` = 'CAPEX') or (`a`.`TAG_NAME` = 'Additional PPE & Intangible'))))) union all (select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`CATEGORY` AS `CATEGORY`,`a`.`UNIT` AS `UNIT`,`a`.`CURRENCY` AS `CURRENCY`,concat(`a`.`TAG_NAME`,'/Total Revenue') AS `ITEM`,(`a`.`VAL` / `b`.`VAL`) AS `VAL`,`a`.`YYYY` AS `YYYY`,concat(`a`.`TAGID`,'/TR') AS `TAGID`,'NA' AS `TAG_NAME`,'CAL_ITEM' AS `DTYPE` from (`test`.`fs_pivot` `a` left join `test`.`fs_pivot` `b` on(((`a`.`CUSTOMER` = `b`.`CUSTOMER`) and (`a`.`YYYY` = `b`.`YYYY`) and (`b`.`ITEM` = 'Total Revenue') and (`b`.`DTYPE` = 'CAL_ITEM')))) where (((`a`.`DTYPE` = 'TAG_ITEM') and ((`a`.`TAGID` = 'T0001') or (`a`.`TAGID` = 'T0005') or (`a`.`TAGID` = 'T0002') or (`a`.`TAGID` = 'T0011') or (`a`.`TAGID` = 'T0010') or (`a`.`TAGID` = 'T0006') or (`a`.`TAGID` = 'T0007') or (`a`.`TAGID` = 'T30001') or (`a`.`TAGID` = 'T0003'))) or ((`a`.`DTYPE` = 'CAL_ITEM') and ((`a`.`TAG_NAME` = 'CAPEX') or (`a`.`TAG_NAME` = 'Additional PPE & Intangible') or (`a`.`TAG_NAME` = 'All Other Expenses(Exclude D&A)') or (`a`.`TAG_NAME` = 'All Accrual Expenses'))))) union all (select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`CATEGORY` AS `CATEGORY`,`a`.`UNIT` AS `UNIT`,`a`.`CURRENCY` AS `CURRENCY`,'Net Cash' AS `ITEM`,(`a`.`VAL` - `b`.`VAL`) AS `VAL`,`a`.`YYYY` AS `YYYY`,'NA' AS `TAGID`,'NA' AS `TAG_NAME`,'CAL_ITEM' AS `DTYPE` from (`test`.`fs_pivot` `a` left join `test`.`fs_pivot` `b` on(((`a`.`CUSTOMER` = `b`.`CUSTOMER`) and (`a`.`YYYY` = `b`.`YYYY`) and (`b`.`TAGID` = 'T0019') and (`b`.`DTYPE` = 'TAG_ITEM')))) where ((`a`.`TAGID` = 'T0018') and (`a`.`DTYPE` = 'TAG_ITEM'))) union all (select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`CATEGORY` AS `CATEGORY`,`a`.`UNIT` AS `UNIT`,`a`.`CURRENCY` AS `CURRENCY`,'Net Cash' AS `ITEM`,(`a`.`VAL` - `b`.`VAL`) AS `VAL`,`a`.`YYYY` AS `YYYY`,'NA' AS `TAGID`,'NA' AS `TAG_NAME`,'CAL_ITEM' AS `DTYPE` from (`test`.`fs_pivot` `a` left join `test`.`fs_pivot` `b` on(((`a`.`CUSTOMER` = `b`.`CUSTOMER`) and (`a`.`YYYY` = `b`.`YYYY`) and (`b`.`TAGID` = 'T0019') and (`b`.`DTYPE` = 'TAG_ITEM')))) where ((`a`.`TAGID` = 'T0018') and (`a`.`DTYPE` = 'TAG_ITEM'))) union all (select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`CATEGORY` AS `CATEGORY`,`a`.`UNIT` AS `UNIT`,`a`.`CURRENCY` AS `CURRENCY`,'Free Cash Flow' AS `ITEM`,(`a`.`VAL` + `b`.`VAL`) AS `VAL`,`a`.`YYYY` AS `YYYY`,'NA' AS `TAGID`,'NA' AS `TAG_NAME`,'CAL_ITEM' AS `DTYPE` from (`test`.`fs_pivot` `a` left join `test`.`fs_pivot` `b` on(((`a`.`CUSTOMER` = `b`.`CUSTOMER`) and (`a`.`YYYY` = `b`.`YYYY`) and (`b`.`ITEM` = 'CAPEX') and (`b`.`DTYPE` = 'CAL_ITEM')))) where ((`a`.`TAGID` = 'T0018') and (`a`.`DTYPE` = 'TAG_ITEM'))) union all (select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`CATEGORY` AS `CATEGORY`,`a`.`UNIT` AS `UNIT`,`a`.`CURRENCY` AS `CURRENCY`,'Net Debt/EBITDA' AS `ITEM`,(`a`.`VAL` + `b`.`VAL`) AS `VAL`,`a`.`YYYY` AS `YYYY`,'NA' AS `TAGID`,'NA' AS `TAG_NAME`,'CAL_ITEM' AS `DTYPE` from (`test`.`fs_pivot` `a` left join `test`.`fs_pivot` `b` on(((`a`.`CUSTOMER` = `b`.`CUSTOMER`) and (`a`.`YYYY` = `b`.`YYYY`) and (`b`.`ITEM` = 'EBITDA') and (`b`.`DTYPE` = 'TAG_ITEM')))) where ((`a`.`ITEM` = 'Net Debt') and (`a`.`DTYPE` = 'CAL_ITEM')));


create view fs_trend_alter as select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`CATEGORY` AS `CATEGORY`,`a`.`UNIT` AS `UNIT`,`a`.`CURRENCY` AS `CURRENCY`,`a`.`ITEM` AS `ITEM`,`a`.`VAL` AS `VAL`,`a`.`YYYY` AS `YYYY`,`a`.`TAGID` AS `TAGID`,`a`.`TAG_NAME` AS `TAG_NAME`,`a`.`DTYPE` AS `DTYPE`,(case when (`a`.`VAL` > 0.5) then 'UP' when (`a`.`VAL` <= 0.5) then 'DOWN' else '-' end) AS `TREND` from (select `fs_sum_2`.`CUSTOMER` AS `CUSTOMER`,`fs_sum_2`.`CATEGORY` AS `CATEGORY`,`fs_sum_2`.`UNIT` AS `UNIT`,`fs_sum_2`.`CURRENCY` AS `CURRENCY`,`fs_sum_2`.`ITEM` AS `ITEM`,`fs_sum_2`.`VAL` AS `VAL`,`fs_sum_2`.`YYYY` AS `YYYY`,`fs_sum_2`.`TAGID` AS `TAGID`,`fs_sum_2`.`TAG_NAME` AS `TAG_NAME`,`fs_sum_2`.`DTYPE` AS `DTYPE` from `test`.`fs_sum_2` where ((`fs_sum_2`.`DTYPE` = 'GT_ITEM') and (`fs_sum_2`.`YYYY` = (year(curdate()) - 1)))) `a`;


create view fs_alter as select `a`.`CUSTOMER` AS `CUSTOMER`,`a`.`TREND` AS `INDICATOR1`,`b`.`TREND` AS `INDICATOR2`,`d`.`TREND` AS `INDICATOR3`,`a`.`ITEM` AS `INDICATOR1_NAME`,`b`.`ITEM` AS `INDICATOR2_NAME`,`d`.`ITEM` AS `INDICATOR3_NAME`,(case when ((`a`.`TREND` = 'UP') and (`b`.`TREND` = 'UP') and (`d`.`TREND` = 'UP')) then 'GREEN' when ((`a`.`TREND` = 'UP') and (`b`.`TREND` = 'UP') and (`d`.`TREND` = 'DOWN')) then 'YELLOW' when ((`a`.`TREND` = 'UP') and (`b`.`TREND` = 'DOWN') and (`d`.`TREND` = 'UP')) then 'YELLOW' when ((`a`.`TREND` = 'UP') and (`b`.`TREND` = 'DOWN') and (`d`.`TREND` = 'DOWN')) then 'YELLOW' when ((`a`.`TREND` = 'DOWN') and (`b`.`TREND` = 'UP') and (`d`.`TREND` = 'UP')) then 'GREEN' when ((`a`.`TREND` = 'DOWN') and (`b`.`TREND` = 'UP') and (`d`.`TREND` = 'DOWN')) then 'YELLOW' when ((`a`.`TREND` = 'DOWN') and (`b`.`TREND` = 'DOWN') and (`d`.`TREND` = 'UP')) then 'YELLOW' when ((`a`.`TREND` = 'DOWN') and (`b`.`TREND` = 'DOWN') and (`d`.`TREND` = 'DOWN')) then 'RED' end) AS `ALERT` from (((select `fs_trend_alter`.`CUSTOMER` AS `CUSTOMER`,`fs_trend_alter`.`CATEGORY` AS `CATEGORY`,`fs_trend_alter`.`UNIT` AS `UNIT`,`fs_trend_alter`.`CURRENCY` AS `CURRENCY`,`fs_trend_alter`.`ITEM` AS `ITEM`,`fs_trend_alter`.`VAL` AS `VAL`,`fs_trend_alter`.`YYYY` AS `YYYY`,`fs_trend_alter`.`TAGID` AS `TAGID`,`fs_trend_alter`.`TAG_NAME` AS `TAG_NAME`,`fs_trend_alter`.`DTYPE` AS `DTYPE`,`fs_trend_alter`.`TREND` AS `TREND` from `test`.`fs_trend_alter` where (`fs_trend_alter`.`ITEM` = 'Total Revenue Growth')) `a` left join (select `fs_trend_alter`.`CUSTOMER` AS `CUSTOMER`,`fs_trend_alter`.`CATEGORY` AS `CATEGORY`,`fs_trend_alter`.`UNIT` AS `UNIT`,`fs_trend_alter`.`CURRENCY` AS `CURRENCY`,`fs_trend_alter`.`ITEM` AS `ITEM`,`fs_trend_alter`.`VAL` AS `VAL`,`fs_trend_alter`.`YYYY` AS `YYYY`,`fs_trend_alter`.`TAGID` AS `TAGID`,`fs_trend_alter`.`TAG_NAME` AS `TAG_NAME`,`fs_trend_alter`.`DTYPE` AS `DTYPE`,`fs_trend_alter`.`TREND` AS `TREND` from `test`.`fs_trend_alter` where (`fs_trend_alter`.`ITEM` = 'Profit After Tax Growth')) `b` on((`a`.`CUSTOMER` = `b`.`CUSTOMER`))) left join (select `fs_trend_alter`.`CUSTOMER` AS `CUSTOMER`,`fs_trend_alter`.`CATEGORY` AS `CATEGORY`,`fs_trend_alter`.`UNIT` AS `UNIT`,`fs_trend_alter`.`CURRENCY` AS `CURRENCY`,`fs_trend_alter`.`ITEM` AS `ITEM`,`fs_trend_alter`.`VAL` AS `VAL`,`fs_trend_alter`.`YYYY` AS `YYYY`,`fs_trend_alter`.`TAGID` AS `TAGID`,`fs_trend_alter`.`TAG_NAME` AS `TAG_NAME`,`fs_trend_alter`.`DTYPE` AS `DTYPE`,`fs_trend_alter`.`TREND` AS `TREND` from `test`.`fs_trend_alter` where (`fs_trend_alter`.`ITEM` = 'EBITDA Growth')) `d` on((`a`.`CUSTOMER` = `d`.`CUSTOMER`)));