~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
mview=SODC_UPTIME_COMPARISON_VW
~~~END_MAIN_SECTION~~~

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

[SQL_SCRIPT___DEFAULT]

CREATE MATERIALIZED VIEW OTHQ_OWN.SODC_UPTIME_COMPARISON_VW refresh on demand start with sysdate next sysdate + 0.1 AS 
    (
        select tmp.*,
               case
                   when tmp.difference > 1        then
                       0
                   when tmp.difference > 0.1      then
                       1
                   when tmp.difference > 0.01     then
                       2
                   when tmp.difference > 0.001    then
                       3
                   when tmp.difference > 0.0001   then
                       4
                   when tmp.difference > 0.00001  then
                       5
                   when tmp.difference > 0.000001 then
                       6
                   when tmp.difference = -1 then
                       -1
				   else 7
               end "ACCURACY"
          from (
            select "DDATE",
                   dcid,
                   dc_name,
                   uptime_calculated,
                   uptime_from_csv,
                   case
                       when uptime_from_csv is null then
                           - 1
                       else
                           abs(uptime_calculated - uptime_from_csv)
                   end "DIFFERENCE",
                   case
                       when uptime_from_csv is null then
                           - 1
                       else
                           case when uptime_calculated=0 then 100 else abs(uptime_calculated - uptime_from_csv) / uptime_calculated * 100 end
                   end "PERCENTAGE_DIFFERENCE"
              from (
                select p_start "DDATE",
                       dcid "DCID",
                       name "DC_NAME",
                       uptime "UPTIME_CALCULATED",
                       case
                           when number_of_customers = 0 then
                               100
                           else
                               ( 100 - down_minutes / ( all_minutes * number_of_customers ) * 100 )
                       end "UPTIME_FROM_CSV"
                  from (select P_START,INACTIVE,DCID,ACCOUNTTYPE,TOP200,sum(UP) as "UP",sum(DOWN) as "DOWN",case when (sum(UP)+sum(DOWN)) = 0 then 101 else (1-(sum(DOWN)/(sum(UP)+sum(DOWN))))*100 end as "UPTIME" from SODC_DOWNTIME_TB group by P_START,INACTIVE,DCID,ACCOUNTTYPE,TOP200)
                  left join src_hub_dc_names_tb
                on dcid = id
                  left join src_csv_state_of_dc_historical_tb csv
                on name = csv.dc
                   and p_start = ddate
                 where inactive = 0
                   and accounttype = 'PRODUCTION'
                   and dcid is not null
                   and p_start <= sysdate
                 order by p_start desc,
                          dcid
            )
        ) tmp
    )
~~~END_VERSION_SECTION~~~