Section 8 data entities

SELECT distinct cYear, q1.countryCode as "country_code", q1.countryCode
, ISNULL(TV9_1, 0) as "TV9_1"
, ISNULL(TV9_2, 0) as "TV9_2"
,ISNULL(TV9_3, 0) as "TV9_3"
,ISNULL(TV9_6, 0) as "TV9_6"
,ISNULL(TV9_7, 0) as "TV9_7"
,ISNULL(TV9_8, 0) as "TV9_8"
,ISNULL(TV9_11, 0) as "TV9_11"
,ISNULL(TV9_12, 0) as "TV9_12"
,ISNULL(TV9_13, 0) as "TV9_13"
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
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'))