--Query: 			Information on WHEN we will require flexibility services, grouped into years and months, starting from the current year (when query is run)
--Data Set:			Flexibility
--Organisation:		National Grid Electricity Distribution – DSO
--Date created:		10/08/2023
--Last updated:		28/08/2025


--Amendment 12/08/2025: Modifying table names and updating the scenario that has been studied.
--Amendment 28/08/2025: Added a script to convert SCENARIO_YEAR column (which is in Financial Year) to Calendar Year. As an example a financial year of 2027 would represent a calendar month and year range of April 2026 to March 2027 inclusive.

--Temporary table to sort months and days of the week in order, and only select the days of actual service need
WITH Sorted_services_required AS (

	SELECT 
		   CMZ_CODE
		  ,SCENARIO
		  ,SCENARIO_YEAR
		  ,MONTH
		  ,WEEKDAY
		  ,BOOL

	FROM SERVICES_REQUIRED

	WHERE 
		-- Only take Holistic Transition scenario
		SCENARIO like '%Holistic Transition%'
		-- Only take days of services required
		AND BOOL = 1

	ORDER BY
		 CMZ_CODE
		,SCENARIO_YEAR
		-- Sort months
		,CASE 
			WHEN MONTH = 'January' THEN 1
			WHEN MONTH = 'February' THEN 2
			WHEN MONTH = 'March' THEN 3
			WHEN MONTH = 'April' THEN 4
			WHEN MONTH = 'May' THEN 5
			WHEN MONTH = 'June' THEN 6
			WHEN MONTH = 'July' THEN 7
			WHEN MONTH = 'August' THEN 8
			WHEN MONTH = 'September' THEN 9
			WHEN MONTH = 'October' THEN 10
			WHEN MONTH = 'November' THEN 11
			WHEN MONTH = 'December' THEN 12
		  END
		  -- sort days of the week
		 ,CASE
			WHEN WEEKDAY = 'Monday' THEN 1
			WHEN WEEKDAY = 'Tuesday' THEN 2
			WHEN WEEKDAY = 'Wednesday' THEN 3
			WHEN WEEKDAY = 'Thursday' THEN 4
			WHEN WEEKDAY = 'Friday' THEN 5
			WHEN WEEKDAY = 'Saturday' THEN 6
			WHEN WEEKDAY = 'Sunday' THEN 7
		 END
		
	),

-- Temporary table to concatenate all days of service required
Weekdays_table AS (

	SELECT CMZ_CODE
		  ,SCENARIO
		  ,SCENARIO_YEAR
		  ,MONTH
		  ,group_concat(WEEKDAY,',') as 'DAYS'
		  
	FROM Sorted_services_required

	GROUP BY
		   CMZ_CODE
		  ,SCENARIO
		  ,SCENARIO_YEAR
		  ,MONTH

	ORDER BY
		 CMZ_CODE
		,SCENARIO_YEAR
		,CASE 
			WHEN MONTH = 'January' THEN 1
			WHEN MONTH = 'February' THEN 2
			WHEN MONTH = 'March' THEN 3
			WHEN MONTH = 'April' THEN 4
			WHEN MONTH = 'May' THEN 5
			WHEN MONTH = 'June' THEN 6
			WHEN MONTH = 'July' THEN 7
			WHEN MONTH = 'August' THEN 8
			WHEN MONTH = 'September' THEN 9
			WHEN MONTH = 'October' THEN 10
			WHEN MONTH = 'November' THEN 11
			WHEN MONTH = 'December' THEN 12
		 END
)

-- Main SELECT statement
SELECT
	 MW_PROFILES.CMZ_CODE as 'CMZ Code'
	,CMZ_INFORMATION.FRIENDLY_NAME as 'Zone name'
	,SCENARIO_LOOKUP.RETURN as 'Scenario'
	,IIF(CASE
					-- Create month number column and convert Scenario Year (which is in Financial Year) to Calendar Year
					WHEN MW_PROFILES.MONTH = 'January' THEN 1
					WHEN MW_PROFILES.MONTH = 'February' THEN 2
					WHEN MW_PROFILES.MONTH = 'March' THEN 3
					WHEN MW_PROFILES.MONTH = 'April' THEN 4
					WHEN MW_PROFILES.MONTH = 'May' THEN 5
					WHEN MW_PROFILES.MONTH = 'June' THEN 6
					WHEN MW_PROFILES.MONTH = 'July' THEN 7
					WHEN MW_PROFILES.MONTH = 'August' THEN 8
					WHEN MW_PROFILES.MONTH = 'September' THEN 9
					WHEN MW_PROFILES.MONTH = 'October' THEN 10
					WHEN MW_PROFILES.MONTH = 'November' THEN 11
					WHEN MW_PROFILES.MONTH = 'December' THEN 12
				END IN (1, 2, 3), MW_PROFILES.SCENARIO_YEAR, MW_PROFILES.SCENARIO_YEAR - 1) as 'Year'
	,MW_PROFILES.MONTH as 'Month'
	,Weekdays_table.DAYS as 'Days required'
	,ROUND(MAX(MW_PROFILES.MW_PROFILE),2) as 'Peak capacity required [MW]'
	,ROUND(COUNT(NULLIF((MW_PROFILES.MW_PROFILE),0))/2.0,1) as 'Potential number of hours per day'

FROM MW_PROFILES

JOIN SCENARIO_LOOKUP
	ON MW_PROFILES.SCENARIO = SCENARIO_LOOKUP.LOOKUP

JOIN CMZ_INFORMATION
	ON MW_PROFILES.CMZ_CODE = CMZ_INFORMATION.CMZ_CODE

JOIN Weekdays_table
	ON MW_PROFILES.CMZ_CODE = Weekdays_table.CMZ_CODE
	AND MW_PROFILES.SCENARIO = Weekdays_table.SCENARIO
	AND MW_PROFILES.SCENARIO_YEAR = Weekdays_table.SCENARIO_YEAR
	AND MW_PROFILES.MONTH = Weekdays_table.MONTH
	
WHERE
	-- Holistic Transition scenario only
	MW_PROFILES.SCENARIO like '%Holistic Transition%'
	-- Only show zones in active procurement
	AND CMZ_INFORMATION.TRANCHE_STATUS like '%Procurement%'
	AND MW_PROFILES.MW_PROFILE > 0

GROUP BY
	MW_PROFILES.CMZ_CODE
	,MW_PROFILES.SCENARIO
	,MW_PROFILES.SCENARIO_YEAR
	,MW_PROFILES.MONTH

ORDER BY
	 MW_PROFILES.CMZ_CODE
	,Year
	-- Sort calendar months
	,CASE 
		WHEN MW_PROFILES.MONTH = 'January' THEN 1
		WHEN MW_PROFILES.MONTH = 'February' THEN 2
		WHEN MW_PROFILES.MONTH = 'March' THEN 3
		WHEN MW_PROFILES.MONTH = 'April' THEN 4
		WHEN MW_PROFILES.MONTH = 'May' THEN 5
		WHEN MW_PROFILES.MONTH = 'June' THEN 6
		WHEN MW_PROFILES.MONTH = 'July' THEN 7
		WHEN MW_PROFILES.MONTH = 'August' THEN 8
		WHEN MW_PROFILES.MONTH = 'September' THEN 9
		WHEN MW_PROFILES.MONTH = 'October' THEN 10
		WHEN MW_PROFILES.MONTH = 'November' THEN 11
		WHEN MW_PROFILES.MONTH = 'December' THEN 12
	 END
