Section 7 EU data entities part2

SELECT distinct 2022 as cYear, 'EU' 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
-- RIGHT OUTER JOIN (select 'EU' as 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'
-- and countryGroup = 'EU27'
-- ) RW
-- on RW.countryCode = q1.countryCode
-- RIGHT OUTER JOIN (select 'EU' as 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'
-- and countryGroup = 'EU27'
-- ) LW
-- on LW.countryCode = RW.countryCode
-- RIGHT OUTER JOIN (select 'EU' as 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'
-- and countryGroup = 'EU27'
-- ) TW
-- on TW.countryCode = LW.countryCode
RIGHT OUTER JOIN (select 'EU' as 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'
and countryGroup = 'EU27'
) CW
on CW.countryCode = q1.countryCode
RIGHT OUTER JOIN (select 'EU' as 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'))
and countryGroup = 'EU27'
) GW
on GW.countryCode = CW.countryCode

No data