~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__ON_DEMAND_SODC_DOWNTIME_PRE2_TB__PROC
table=TMP_ON_DEMAND_SODC_DOWNTIME_PRE2_TB
table=ON_DEMAND_SODC_DOWNTIME_PRE2_TB
~~~END_MAIN_SECTION~~~

~~~BEGIN_VERSION_SECTION~~~
[VERSION]
version=2025-11-04

[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__ON_DEMAND_SODC_DOWNTIME_PRE2_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_ON_DEMAND_SODC_DOWNTIME_PRE2_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
	EXECUTE IMMEDIATE q'!
	CREATE TABLE "OTHQ_OWN"."TMP_ON_DEMAND_SODC_DOWNTIME_PRE2_TB" 
	   (	"P_START" DATE, 
		"CATEGORY" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
		"SMILEY_TYPE" NUMBER, 
		"CNT" NUMBER, 
		"PERCENTAGE" NUMBER, 
		"PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
		"PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP"
		)!';
	end;
	commit;
	begin
	EXECUTE IMMEDIATE q'!
	insert into TMP_ON_DEMAND_SODC_DOWNTIME_PRE2_TB 
	(select
		P_START
		,CATEGORY
		,SMILEY_TYPE
		,CNT
		,PERCENTAGE
		,case
			when PERCENTAGE<1 then '<1%'
			else ROUND(PERCENTAGE) || '%'
		end PERCENTAGE1
		,case
			when PERCENTAGE<1 then '<1%' || ' (' || CNT || ')'
			else ROUND(PERCENTAGE) || '%' || ' (' || CNT || ')'
		end PERCENTAGE2
		--,ID
	from
		(select
			P_START
			,CATEGORY
			,SMILEY_TYPE
			--,sum(UP)
			--,sum(DOWN)
			--,case when sum(UP)=0 then 100 else 100-(sum(DOWN)/sum(UP)*100) END "UPTIME"
			,count(*) CNT
			--,sum(count(*)) over (PARTITION BY P_START,CATEGORY)
			,count(*)/sum(count(*)) over (PARTITION BY P_START,CATEGORY)*100 PERCENTAGE    
		from
		(
			select
				P_START
				,CATEGORY
				,COMPID
				,sum("UP") "UP"
				,sum("DOWN") "DOWN"
				,case when sum("UP")=0 then 100 else 100-(sum("DOWN")/sum("UP")*100) END "UPTIME"
				,case
					when (case when sum("UP")=0 then 100 else 100-(sum("DOWN")/sum("UP")*100) END) between 0 and 99 then 4
					when (case when sum("UP")=0 then 100 else 100-(sum("DOWN")/sum("UP")*100) END) between 99 and 99.5 then 3
					when (case when sum("UP")=0 then 100 else 100-(sum("DOWN")/sum("UP")*100) END) between 99.5 and 99.7 then 2
					when (case when sum("UP")=0 then 100 else 100-(sum("DOWN")/sum("UP")*100) END) between 99.7 and 99.99 then 1
					when (case when sum("UP")=0 then 100 else 100-(sum("DOWN")/sum("UP")*100) END) >= 99.99 then 0
					else -1
				end "SMILEY_TYPE"
			from
			(
				select 
					P_START
					--,INACTIVE
					--,DCID
					--,ACCOUNTTYPE
					,CATEGORY
					,COMPID
					--,DOWNTIME
					--,1 TOP200
					,sum(case when DOWNTIME=0 then (DURATION) else 0 END) "UP"
					,sum(case when DOWNTIME=1 then (DURATION) else 0 END) "DOWN"
					,case when (sum(case when DOWNTIME=0 then (DURATION) else 0 END))=0 then 100
					else 100-(sum(case when DOWNTIME=1 then (DURATION) else 0 END)/sum(case when DOWNTIME=0 then (DURATION) else 0 END)*100) END "UPTIME"
				from ON_DEMAND_SODC_DOWNTIME_PRE1_WOSMD_TB
				where 
					INACTIVE=0 and ACCOUNTTYPE='PRODUCTION' and CATEGORY in ('ATP','EXA')
				group by P_START/*,INACTIVE,DCID,ACCOUNTTYPE*/,CATEGORY,COMPID
				--,DOWNTIME
				--,TOP200
				--order by P_START,DCID/*,ACCOUNTTYPE*/,CATEGORY
			)
			--where p_start=to_date('2016-03-01 00:00:00') and dcid='sjc-na14' and ACCOUNTTYPE='PRODUCTION' and CATEGORY='NONE'
			group by P_START,CATEGORY,COMPID
		)
		group by P_START,CATEGORY,SMILEY_TYPE
		order by P_START desc, CATEGORY,SMILEY_TYPE)
	)!';
	end;
	commit;
	begin EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_SODC_DOWNTIME_PRE2_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin EXECUTE IMMEDIATE 'rename TMP_ON_DEMAND_SODC_DOWNTIME_PRE2_TB to ON_DEMAND_SODC_DOWNTIME_PRE2_TB';exception WHEN OTHERS THEN NULL;end;commit;
	--begin EXECUTE IMMEDIATE 'GRANT SELECT ON OTHQ_OWN.ON_DEMAND_SODC_DOWNTIME_PRE2_TB TO USER_STATE_OF_DATACENTER';end;commit;
end;
~~~END_VERSION_SECTION~~~