--Query: 			Information on WHEN we will require flexibility services, shown as a monthly half-hourly MW profile for the next 12 months (including current month)
--Data Set:			Flexibility
--Organisation:		National Grid Electricity Distribution � DSO
--Date created:		21/08/2023
--Last updated:		28/08/2025

--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.

WITH profiles AS ( 

		SELECT MW_PROFILES.CMZ_CODE
			  ,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 CALENDAR_YEAR
			  ,MW_PROFILES.MONTH
			  ,CASE -- Create month number column
					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 as 'MONTH_NUMBER'
			  ,MW_PROFILES.HH
			  ,CASE MW_PROFILES.HH -- Map each half hour to its corresponding time
					WHEN 1  THEN '00:30' WHEN 2  THEN '01:00' WHEN 3  THEN '01:30'	WHEN 4  THEN '02:00' WHEN 5  THEN '02:30' WHEN 6  THEN '03:00'
					WHEN 7  THEN '03:30' WHEN 8  THEN '04:00' WHEN 9  THEN '04:30'	WHEN 10 THEN '05:00' WHEN 11 THEN '05:30' WHEN 12 THEN '06:00'
					WHEN 13 THEN '06:30' WHEN 14 THEN '07:00' WHEN 15 THEN '07:30'  WHEN 16 THEN '08:00' WHEN 17 THEN '08:30' WHEN 18 THEN '09:00'
					WHEN 19 THEN '09:30' WHEN 20 THEN '10:00' WHEN 21 THEN '10:30'	WHEN 22 THEN '11:00' WHEN 23 THEN '11:30' WHEN 24 THEN '12:00'				
					WHEN 25 THEN '12:30' WHEN 26 THEN '13:00' WHEN 27 THEN '13:30'	WHEN 28 THEN '14:00' WHEN 29 THEN '14:30' WHEN 30 THEN '15:00'
					WHEN 31 THEN '15:30' WHEN 32 THEN '16:00' WHEN 33 THEN '16:30'	WHEN 34 THEN '17:00' WHEN 35 THEN '17:30' WHEN 36 THEN '18:00'
					WHEN 37 THEN '18:30' WHEN 38 THEN '19:00' WHEN 39 THEN '19:30'  WHEN 40 THEN '20:00' WHEN 41 THEN '20:30' WHEN 42 THEN '21:00'
					WHEN 43 THEN '21:30' WHEN 44 THEN '22:00' WHEN 45 THEN '22:30'	WHEN 46 THEN '23:00' WHEN 47 THEN '23:30' WHEN 48 THEN '00:00'
					END as 'END_TIME'
			  ,MW_PROFILES.MW_PROFILE

		FROM MW_PROFILES
		
		JOIN SCENARIO_LOOKUP
			ON MW_PROFILES.SCENARIO = SCENARIO_LOOKUP.LOOKUP
		
		INNER JOIN CMZ_INFORMATION															--inner join CMZ_INFORMATION table using CMZ_CODE
			ON MW_PROFILES.CMZ_CODE = CMZ_INFORMATION.CMZ_CODE

		WHERE
			MW_PROFILES.SCENARIO like '%Holistic Transition%'
			AND CMZ_INFORMATION.TRANCHE_STATUS = 'PROCUREMENT'
)
			  
SELECT CMZ_CODE as 'CMZ Code'
	  ,SCENARIO as 'Scenario'
	  ,CALENDAR_YEAR as 'Year'
	  ,MONTH as 'Month'
	  ,HH as 'Half Hour'
	  ,TIME(END_TIME,'-30 minutes') as 'Start time'
	  ,TIME(END_TIME) as 'End time'
	  ,ROUND(MW_PROFILE,2) as 'Capacity required [MW]'

FROM profiles

ORDER BY CMZ_CODE, CALENDAR_YEAR, MONTH_NUMBER

