--Query:			Information on flexibility volumes estimated (MW, MWh) under the Best View scenario, starting from current year (when query is run)
--Data Set:			Flexibility
--Organisation:		National Grid Electricity Distribution – DSO
--Date created:		10/08/2023
--Last updated:		12/08/2025

--Amendments
--03/01/2024: Converting year to INT on line 36
--12/08/2025: Modifying table names and updating the scenario that has been studied. 
--Note that data from tables with a suffix of _FINANCIAL_YEAR  mean that the "SCENARIO_YEAR" is a financial year, as an example a financial year of 2027 would represent a calendar month and year range of April 2026 to March 2027 inclusive.

SELECT FINANCIAL_RESULTS.CMZ_CODE as 'CMZ Code'
	  ,CMZ_INFORMATION.FRIENDLY_NAME as 'Zone name'
	  ,SCENARIO_LOOKUP.RETURN as 'Scenario'
	  ,FINANCIAL_RESULTS.SCENARIO_YEAR as 'Financial Year'
	  ,FINANCIAL_RESULTS.PRODUCT as 'Main flexibility product'
	  ,FINANCIAL_RESULTS.PEAK_MW as 'Peak capacity required [MW]'
	  ,FINANCIAL_RESULTS.AVAILABILITY_ENERGY as 'Estimated availability energy [MWh]' 
	  ,FINANCIAL_RESULTS.UTILISATION_ENERGY as 'Estimated utilisation energy [MWh]'
	  ,FINANCIAL_RESULTS.AVAILABILITY_RATE as 'Availability ceiling price [£/MW/h]'
	  ,FINANCIAL_RESULTS.UTILISATION_RATE as 'Utilisation ceiling price [£/MWh]'

FROM FINANCIAL_RESULTS

JOIN SCENARIO_LOOKUP
	ON FINANCIAL_RESULTS.SCENARIO = SCENARIO_LOOKUP.LOOKUP

INNER JOIN CMZ_INFORMATION															
	ON FINANCIAL_RESULTS.CMZ_CODE = CMZ_INFORMATION.CMZ_CODE

WHERE
	-- Only zones in procurement
	CMZ_INFORMATION.TRANCHE_STATUS = 'PROCUREMENT'
	-- Only for the Best View scenario
	AND FINANCIAL_RESULTS.SCENARIO like '%Holistic Transition%'
	-- Starting from current year
	AND FINANCIAL_RESULTS.SCENARIO_YEAR >= CAST(strftime('%Y', 'now') as int)

ORDER BY
	FINANCIAL_RESULTS.CMZ_CODE,
	FINANCIAL_RESULTS.SCENARIO_YEAR
