5d.392. Used Quota (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 trange as (
  select /*+ materialize */ 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
),
t1 AS (
  SELECT /*+ materialize */ SUM(COSTS$COMPUTEDAMOUNT) COMPUTEDAMOUNT,
         TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') TIMEUTC
  FROM   OCI360_USAGECOSTS
  GROUP BY TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')
),
tcycle AS (
  SELECT /*+ materialize */ PURCHASE$PURCHASEDRESOURCES$VALUE PURCHASEDRESOURCES,
         TO_TIMESTAMP(PURCHASE$PURCHASEDRESOURCES$STARTDATE,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') STARTDATE,
         TO_TIMESTAMP(PURCHASE$PURCHASEDRESOURCES$ENDDATE,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') ENDDATE
  FROM   OCI360_ACCOUNTDETAILS, trange
  WHERE  PURCHASE$ID is not null
    AND  PURCHASE$PURCHASEDRESOURCES$NAME = 'CLOUD_CREDIT_AMOUNT'
    AND  TO_TIMESTAMP(PURCHASE$PURCHASEDRESOURCES$STARTDATE,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') > trange.min
),
allhours as ( /*  Will generate all hours between Min and Max Start Time */
  SELECT /*+ materialize */ trange.min + (rownum - 1)/24 vdate,
         rownum seq
  FROM   trange
  WHERE  trange.min + (rownum - 1)/24 <= trange.max - 1/24 /*  Skip last hour as can be an incompleted current hour. */
  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(CEIL(tcycle.PURCHASEDRESOURCES*100)/100,'99999990D00')  line1,
       TO_CHAR(CEIL(SUM(t1.COMPUTEDAMOUNT)*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   t1, tcycle, allhours
WHERE  vdate between tcycle.STARTDATE AND tcycle.ENDDATE /*  For each hour frame I get the billing period range */
AND    t1.TIMEUTC >= tcycle.STARTDATE AND t1.TIMEUTC < vdate+1/24 /*  The Billing end time must be inside the range until the computed hour */
group by seq, vdate, PURCHASEDRESOURCES
order by seq;


0 rows selected.