Section 8 data entities part2

SELECT distinct cYear, q1.countryCode as "country_code"
-- , ISNULL(TV9_1, 0) as TV9_1
-- , ISNULL(TV9_2, 0) as TV9_2
-- ,ISNULL(TV9_3, 0) as TV9_3
,ISNULL(TV9_4, 0) as "TV9_4"
,ISNULL(TV9_5, 0) as "TV9_5"
-- ,ISNULL(TV9_6, 0) as TV9_6
-- ,ISNULL(TV9_7, 0) as TV9_7
-- ,ISNULL(TV9_8, 0) as TV9_8
,ISNULL(TV9_9, 0) as "TV9_9"
,ISNULL(TV9_10, 0) as "TV9_10"
-- ,ISNULL(TV9_11, 0) as TV9_11
-- ,ISNULL(TV9_12, 0) as TV9_12
-- ,ISNULL(TV9_13, 0) as TV9_13
,ISNULL(TV9_14, 0) as "TV9_14"
,ISNULL(TV9_15, 0) as "TV9_15"
from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as "q1"
-- FULL OUTER JOIN (select countryCode
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_1'
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_6'
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_11'
-- from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q2 
-- where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
-- or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
-- and surfaceWaterBodyCategory = 'RW'
-- group by countryCode) RW
-- on RW.countryCode = q1.countryCode
-- FULL OUTER JOIN (select countryCode
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_2'
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_7'
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_12'
-- from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q2 
-- where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
-- or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
-- and surfaceWaterBodyCategory = 'LW'
-- group by countryCode) LW
-- on LW.countryCode = q1.countryCode
-- FULL OUTER JOIN (select countryCode
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_3'
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_8'
-- ,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as 'TV9_13'
-- from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as q2 
-- where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
-- or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
-- and surfaceWaterBodyCategory = 'TW'
-- group by countryCode) TW
-- on TW.countryCode = q1.countryCode
FULL OUTER JOIN (select countryCode
,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as "TV9_4"
,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as "TV9_9"
,ROUND(CAST(COUNT(CASE WHEN swSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN fileUrl ELSE null END) as float)/CAST(COUNT(distinct euSurfaceWaterBodyCode) as float)*100, 2) as "TV9_14"
from WISE_WFD.latest.SWB_SurfaceWaterBody_swSignificantPressureType as "q2" 
where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
and surfaceWaterBodyCategory = 'CW'
group by countryCode) CW
on CW.countryCode = q1.countryCode
FULL OUTER JOIN (select countryCode
,ROUND(CAST(SUM(CASE WHEN gwSignificantPressureType = 'P1-1 - Point - Urban waste water' THEN cArea ELSE null END) as float)/CAST(SUM(distinct cArea) as float)*100, 2) as "TV9_5"
,ROUND(CAST(SUM(CASE WHEN gwSignificantPressureType = 'P2-6 - Diffuse - Discharges not connected to sewerage network' THEN cArea ELSE null END) as float)/CAST(SUM(distinct cArea) as float)*100, 2) as "TV9_10"
,ROUND(CAST(SUM(CASE WHEN gwSignificantPressureType = 'P1-2 - Point - Storm overflows' THEN cArea ELSE null END) as float)/CAST(SUM(distinct cArea) as float)*100, 2) as "TV9_15"
from WISE_WFD.latest.GWB_GroundWaterBody_gwSignificantPressureType as "q2" 
where ((countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
or (countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))
group by countryCode) GW
on GW.countryCode = q1.countryCode
where ((q1.countryCode not in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2022')
or (q1.countryCode in ('BG', 'FI', 'HU', 'MT', 'SI') and cYear = '2016'))