5e.1.437. Service Name: COMPUTEBAREMETAL, between 2020-04-18 and 2020-06-28 (OCI360_USAGECOSTS)

Notes:
1) drag to zoom, and right click to reset
2) up to 31 days of awr history were considered
3)
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 COSTS$UNITPRICE                                                                              NUMBER
 COSTS$OVERAGESFLAG                                                                           VARCHAR2(1)
 COSTS$COMPUTEDAMOUNT                                                                         NUMBER
 COSTS$COMPUTEDQUANTITY                                                                       NUMBER
 CURRENCY                                                                                     VARCHAR2(4)
 QUANTITY                                                                                     NUMBER
 ENDTIMEUTC                                                                                   VARCHAR2(32)
 SERVICENAME                                                                                  VARCHAR2(32)
 DATACENTERID                                                                                 VARCHAR2(16)
 GSIPRODUCTID                                                                                 VARCHAR2(8)
 RESOURCENAME                                                                                 VARCHAR2(64)
 STARTTIMEUTC                                                                                 VARCHAR2(32)
 SUBSCRIPTIONID                                                                               VARCHAR2(8)
 SUBSCRIPTIONTYPE                                                                             VARCHAR2(16)
 LASTCOMPUTATIONDATE                                                                          VARCHAR2(32)
 SERVICEENTITLEMENTID                                                                         VARCHAR2(16)


WITH t1 AS (
  SELECT SUM(COSTS$COMPUTEDAMOUNT) COMPUTEDAMOUNT,
         TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') ENDTIMEUTC
  FROM   OCI360_USAGECOSTS
  WHERE  SERVICENAME = 'COMPUTEBAREMETAL'
  GROUP BY TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')
),
trange as (
  select trunc(min(TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')),'HH24') min,
         trunc(max(TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')),'HH24') max
  FROM   OCI360_USAGECOSTS
),
allhours as ( /*  Will generate all hours between Min and Max Start Time */
  SELECT trange.min + (rownum - 1)/24 vdate,
         rownum seq
  FROM   trange
  WHERE  trange.min + (rownum - 1)/24 <= trange.max - 1/24  /*  Skip last entry as may be incomplete. */
  CONNECT BY LEVEL <= (trange.max - trange.min)*24 + 1
)
select seq              snap_id,
       TO_CHAR(vdate,     'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(vdate+1/24,'YYYY-MM-DD HH24:MI') end_time,
       TO_CHAR(NVL(CEIL(SUM(COMPUTEDAMOUNT)*100)/100,0),'99999990D00') line1,
       0                   dummy_02,
       0                   dummy_03,
       0                   dummy_04,
       0                   dummy_05,
       0                   dummy_06,
       0                   dummy_07,
       0                   dummy_08,
       0                   dummy_09,
       0                   dummy_10,
       0                   dummy_11,
       0                   dummy_12,
       0                   dummy_13,
       0                   dummy_14,
       0                   dummy_15
from   t1, allhours
where  ENDTIMEUTC(+) >= vdate and ENDTIMEUTC(+)  < vdate+1/24
group by seq, vdate
order by seq;


1683 rows selected.