Calculations doccumentation for the SEBI007/CSI008 NATIONALLY DESIGNATED PROTECTED AREAS
https://www.eea.europa.eu/data-and-maps/indicators/nationally-designated-protected-areas-11/assessment

October 2020

--------
--- CALCULATIONS EEA39
--------

print 'Count of sites per year and running total (legalFoundationDate) - without NULL values: EEA39';

WITH g AS 
(
SELECT [legalFoundationDate], count_of_sitesEEA39 = COUNT([UID]) 
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570')
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], count_of_sitesEEA39, 
running_total_siteCountEEA39 = SUM(count_of_sitesEEA39) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];


print 'Area of sites per year and running total (siteArea) - without NULL values: EEA39';

WITH g AS 
(
SELECT [legalFoundationDate], area_of_sitesEEA39 = SUM(siteArea)
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570')
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], area_of_sitesEEA39, 
running_total_km2EEA39 = SUM(area_of_sitesEEA39/100.000) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];

--------
--- CALCULATIONS EEA38
--------

print 'Count of sites per year and running total (legalFoundationDate) - without NULL values: EEA38';

WITH g AS 
(
SELECT [legalFoundationDate], count_of_sitesEEA38 = COUNT([UID]) 
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570') and
[cddaCountryCode] <> 'UK'
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], count_of_sitesEEA38, 
running_total_siteCountEEA38 = SUM(count_of_sitesEEA38) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];


print 'Area of sites per year and running total (siteArea) - without NULL values: EEA38';

WITH g AS 
(
SELECT [legalFoundationDate], area_of_sitesEEA38 = SUM(siteArea)
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570') and
[cddaCountryCode] <> 'UK'
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], area_of_sitesEEA38, 
running_total_km2EEA38 = SUM(area_of_sitesEEA38/100.000) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];


--------
--- CALCULATIONS EU28
--------


print 'Count of sites per year and running total (legalFoundationDate) - without NULL values: EU28';

WITH g AS 
(
SELECT [legalFoundationDate], count_of_sitesEU28 = COUNT([UID]) 
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and 
[cddaCountryCode] in ('AT','BE','BG','CY','CZ','DE','DK','EE','EL','ES','FI','FR','HR','HU','IE','IS','IT','LI','LT','LU','LV','MT','NL','PL','PT','RO','SE','UK') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570')
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], count_of_sitesEU28, 
running_total_siteCountEU28 = SUM(count_of_sitesEU28) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];

 

print 'Area of sites per year and running total (siteArea) - without NULL values: EU28';

WITH g AS 
(
SELECT [legalFoundationDate], area_of_sitesEU28 = SUM(siteArea)
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and
[cddaCountryCode] in ('AT','BE','BG','CY','CZ','DE','DK','EE','EL','ES','FI','FR','HR','HU','IE','IS','IT','LI','LT','LU','LV','MT','NL','PL','PT','RO','SE','UK') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570')
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], area_of_sitesEU28, 
running_total_km2EU28 = SUM(area_of_sitesEU28/100.000) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];

--------
--- CALCULATIONS EU27
--------

print 'Count of sites per year and running total (legalFoundationDate) - without NULL values: EU27';

WITH g AS 
(
SELECT [legalFoundationDate], count_of_sitesEU27 = COUNT([UID]) 
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and 
[cddaCountryCode] in ('AT','BE','BG','CY','CZ','DE','DK','EE','EL','ES','FI','FR','HR','HU','IE','IS','IT','LI','LT','LU','LV','MT','NL','PL','PT','RO','SE') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570')
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], count_of_sitesEU27, 
running_total_siteCountEU27 = SUM(count_of_sitesEU27) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];

 

print 'Area of sites per year and running total (siteArea) - without NULL values: EU27';

WITH g AS 
(
SELECT [legalFoundationDate], area_of_sitesEU27 = SUM(siteArea)
FROM [CDDA].[data].[DesignatedArea] where 
[legalFoundationDate] is not null and 
cddaRegionCode not in ( 'GL','BL','GL','GF','GP','MF','MQ','YT','RE','PM','PF','TF','SJ') and 
[cddaCountryCode] in ('AT','BE','BG','CY','CZ','DE','DK','EE','EL','ES','FI','FR','HR','HU','IE','IS','IT','LI','LT','LU','LV','MT','NL','PL','PT','RO','SE') and 
PSlocalId not in ('VV00002565','VV00002555','VV00002568','VV00002577','VV00002572','VV00002557','VV00002558','VV00002563','VV00002582','VV00002559','VV00002569','VV00003010','VV00002573','VV00002581','VV00002564','VV00002584','VV00002576','VV00002580','VV00002562','VV00002561','VV00002556','VV00002579','VV00002566','VV00002574','VV00002578' ,'VV00002571','VV00002575','VV00002560','VV00002567','VV00002570')
GROUP BY [legalFoundationDate]
)
SELECT g.[legalFoundationDate], area_of_sitesEU27, 
running_total_km2EU27 = SUM(area_of_sitesEU27/100.000) OVER (ORDER BY [legalFoundationDate] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[legalFoundationDate];