All official European Union website addresses are in the europa.eu domain.
See all EU institutions and bodies
An official website of the European Union | How do you know?
Environmental information systems
Section 7 EU data entities
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 = TW.countryCode
-- RIGHT OUTER JOIN (select 'EU' as countryCode
-- ,ROUND(CAST(COUNT(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(COUNT(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(COUNT(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.countryCodeNo data