SELECT
	a.SITES,
	a.MINISTRY_AGENCY,
	a.MINISTRY,
	a.AGENCY,
	a.SLG,
	isnull(SERVICE_AVAILABILITY, 100.0) AS SERVICE_AVAILABILITY,
	a.spectrum_speed,
	Data_Loading_DATETIME

FROM
	(
		SELECT DISTINCT
			(slg.Site_Name) AS SITES,
			slg.MINISTRY_AGENCY,
			slg.MINISTRY,
			slg.AGENCY,
			slg.SLG,
			CASE
		WHEN Site_Availability >= 99.955555 THEN
			CAST (
				SUBSTRING (
					CONVERT (
						VARCHAR (10),
						Site_Availability
					),
					1,
					4
				) AS DECIMAL (18, 1)
			)
		ELSE
			CAST (
				Round(Site_Availability, 2, 1) AS DECIMAL (18, 1)
			)
		END AS SERVICE_AVAILABILITY,
		spectrum_speed,
		getdate() AS DATA_LOADING_DATETIME
	FROM
		[GITN_SD_STAGING].[dbo].[L_Ministry_Agency_Sites] slg
	LEFT JOIN (
		SELECT
			Ministry,
			Ministry_Agency,
			Agency,
			Agency_Name,
			SiteId,
			Hours_In_Month,
			SUM (RT) AS RT,
			SUM (
				(
					(Hours_In_Month - RT) / Hours_In_Month
				) * 100
			) AS Site_Availability
		FROM
			(
				SELECT
					Ministry,
					Ministry_Agency,
					Agency,
					Agency_Name,
					SiteId,
					CASE
				WHEN MONTH (getdate() - 1) = 1 THEN
					744
				WHEN MONTH (getdate() - 1) = 2 THEN
					696
				WHEN MONTH (getdate() - 1) = 3 THEN
					744
				WHEN MONTH (getdate() - 1) = 4 THEN
					720
				WHEN MONTH (getdate() - 1) = 5 THEN
					744
				WHEN MONTH (getdate() - 1) = 6 THEN
					720
				WHEN MONTH (getdate() - 1) = 7 THEN
					744
				WHEN MONTH (getdate() - 1) = 8 THEN
					744
				WHEN MONTH (getdate() - 1) = 9 THEN
					720
				WHEN MONTH (getdate() - 1) = 10 THEN
					744
				WHEN MONTH (getdate() - 1) = 11 THEN
					720
				ELSE
					744
				END AS 'Hours_In_Month',
				SUM (RT) AS RT
			FROM
				(
					SELECT
						main2.Ministry,
						main2.Ministry_Agency,
						main.Agency_Name,
						SiteId,
						main2.Agency,
						main2.Site_Name,
						SUM (main.RT) AS RT
					FROM
						(
							SELECT
								Agency,
								Agency_Name,
								SiteId,
								RT
							FROM
								[GITN_SD_STAGING].dbo.gitn_sd_reports_staging_temp3
							WHERE
								Ticket_Cat IN ('CR')
							AND CLOSURE_CODE NOT IN (
								'GITN FIREWALL',
								'PCN',
								'USER',
								'OTHERS',
								'GITN-Bandwidth Manager',
								'GITN-MSS Antivirus',
								'GITN-PKI',
								'GITN-VPN Bonded Broadband',
								'GITN-VSAT (SCOPETEL)',
								'GITN-ROUTER'
							)
							AND CLOSURE_CODE2 NOT IN (
								'GITN FIREWALL',
								'PCN',
								'USER',
								'OTHERS',
								'GITN-Bandwidth Manager',
								'GITN-MSS Antivirus',
								'GITN-PKI',
								'GITN-VPN Bonded Broadband',
								'GITN-VSAT (SCOPETEL)',
								'GITN-ROUTER'
							)
						) main
					INNER JOIN [GITN_SD_STAGING].[dbo].[L_Ministry_Agency_Sites] main2 ON main.SiteId = main2.Site_Id
					GROUP BY
						main2.Ministry,
						main2.Ministry_Agency,
						SiteId,
						main2.Agency,
						main2.Site_Name,
						main.Agency_Name
				) a
			GROUP BY
				Ministry,
				Ministry_Agency,
				Agency,
				SiteId,
				Agency_Name
			) b
		GROUP BY
			Ministry,
			Ministry_Agency,
			Agency,
			SiteId,
			Hours_In_Month,
			Agency_Name
	) slg1 ON slg.Site_Id = slg1.SiteId
	) a
INNER JOIN [GITN_CA_STAGING].[dbo].[VW_L_FLOORSWITCH] b ON a.Ministry_Agency = b.Ministry_Agency
AND a.Ministry = b.Ministry
AND a.Agency = b.Agency
AND a.Sites = b.Alias
ORDER BY
	a.Ministry_Agency