--Query: 			Returns list of postcodes for each HV CMZ based on the primary substations within the CMZ. Only zones that are or will shortly be open for PROCUREMENT
--Data Set:			Flexibility
--Organisation:		National Grid Electricity Distribution – DSO
--Date created:		11/06/2021
--Last updated:		29/08/2023

--28/04/2023 Update: amended line 18 from -3 to -2 to show all 4 characters of the postcode
--14/08/2025 Update: added line 39 to filter out incomplete postcodes

SELECT 
	--statement to format postcode
	CASE
	--check if there is already a space in the postcode - just return the post code
		WHEN instr(POSTCODE_LOOKUP.Postcode, ' ') > 1 then 
		POSTCODE_LOOKUP.Postcode
	--check if there are no spaces - format the postcode
		WHEN instr(POSTCODE_LOOKUP.Postcode, ' ') = 0 then 
		substr(POSTCODE_LOOKUP.Postcode, 0, length(POSTCODE_LOOKUP.Postcode)-2) || ' ' || substr(POSTCODE_LOOKUP.Postcode, length(POSTCODE_LOOKUP.Postcode)-2)
	END 							AS 'Postcode',
	
	--data columns selected and renamed
	CMZ_INFORMATION.CMZ_CODE 		AS 'HV CMZ Code',
	CMZ_INFORMATION.FRIENDLY_NAME	AS 'HV Zone Name',
	CMZ_INFORMATION.LICENCE_AREA 	AS 'HV Zone Licence Area',
	CMZ_INFORMATION.PRODUCT 		AS 'HV Zonne Flexibility Product',
	PRIMARY_SUBSTATIONS.NAME 		AS 'Primary Substation Name',
	PRIMARY_SUBSTATIONS.PRIM_NRID 	,
	PRIMARY_SUBSTATIONS.NR 			AS 'Primary Substation Number' 

FROM POSTCODE_LOOKUP	
															--startd with POSTCODE_LOOKUP table and filter down the results of interest
INNER JOIN PRIMARY_SUBSTATIONS        												--inner join PRIMARY_SUBSTATIONS table using PRIM_NRID
		ON	PRIMARY_SUBSTATIONS.PRIM_NRID = POSTCODE_LOOKUP.PRIM_NRID

		INNER JOIN CMZ_INFORMATION															--inner join CMZ_INFORMATION table using CMZ_CODE
		ON PRIMARY_SUBSTATIONS.CMZ_CODE = CMZ_INFORMATION.CMZ_CODE

--filter results of interest
WHERE CMZ_INFORMATION.TRANCHE_STATUS = 'PROCUREMENT' 
	AND length(replace(trim(POSTCODE_LOOKUP.Postcode), ' ', '')) >=5


