5e.2.483. Resource: Block Volumes, between 2020-04-19 and 2020-06-29 (OCI360_USAGECOSTS)

 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  RESOURCENAME = 'PIC_BLOCK_STORAGE_STANDARD'
  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
),
alldays as ( /*  Will generate all days between Min and Max Start Time */
  SELECT trunc(trange.min,'DD') + (rownum - 1) vdate,
         rownum seq
  FROM   trange
  WHERE  trange.min + (rownum - 1) <= trange.max - 1  /*  Skip last entry as may be incomplete. */
  CONNECT BY LEVEL <= (trange.max - trange.min) + 1
),
result as (
  select seq              snap_id,
         TO_CHAR(vdate,  'YYYY-MM-DD HH24:MI') begin_time,
         TO_CHAR(vdate+1,'YYYY-MM-DD HH24:MI') end_time,
         TO_CHAR(NVL(CEIL(SUM(COMPUTEDAMOUNT)*100)/100,0),'99999990D00') line1
  from   t1, alldays
  where  ENDTIMEUTC(+) >= vdate and ENDTIMEUTC(+) < vdate+1
  group by seq, vdate
),
statistics as (
  select REGR_SLOPE(line1,snap_id) slope,
         REGR_INTERCEPT(line1,snap_id) intercept
  from   result
)
select snap_id,
       begin_time,
       end_time,
       line1,
       TO_CHAR(CEIL((slope*snap_id+intercept)*100)/100,'99999990D00') line2,
       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   result, statistics
order by snap_id;


70 rows selected.