SQL-29837218i37612838723867

From Anis-Brillante, 7 Years ago, written in SQL.

URL https://pastebin.pl/view/db2f2007

Embed

Download Paste or View Raw
Hits: 266

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

Author

Title

Language

Your paste - Paste your paste here

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 &gt;= 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

Private - Private paste aren't shown in recent listings.

Delete After - When should we delete your paste?

Spam protection -

{"html5":"htmlmixed","css":"css","javascript":"javascript","php":"php","python":"python","ruby":"ruby","lua":"text\/x-lua","bash":"text\/x-sh","go":"go","c":"text\/x-csrc","cpp":"text\/x-c++src","diff":"diff","latex":"stex","sql":"sql","xml":"xml","apl":"apl","asterisk":"asterisk","c_loadrunner":"text\/x-csrc","c_mac":"text\/x-csrc","coffeescript":"text\/x-coffeescript","csharp":"text\/x-csharp","d":"d","ecmascript":"javascript","erlang":"erlang","groovy":"text\/x-groovy","haskell":"text\/x-haskell","haxe":"text\/x-haxe","html4strict":"htmlmixed","java":"text\/x-java","java5":"text\/x-java","jquery":"javascript","mirc":"mirc","mysql":"sql","ocaml":"text\/x-ocaml","pascal":"text\/x-pascal","perl":"perl","perl6":"perl","plsql":"sql","properties":"text\/x-properties","q":"text\/x-q","scala":"scala","scheme":"text\/x-scheme","tcl":"text\/x-tcl","vb":"text\/x-vb","verilog":"text\/x-verilog","yaml":"text\/x-yaml","z80":"text\/x-z80"}

SQL-29837218i37612838723867

Reply to "SQL-29837218i37612838723867"