5e.4.576. Total per cycle, between 2020-04-19 and 2020-06-29 (OCI360_USAGECOSTS)

#: click on a column heading to sort on it

 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  1 = 1
  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
),
tcycle_account AS (
  SELECT /*+ materialize */
         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'
  ORDER BY STARTDATE
),
tcycle_gaps AS (
  SELECT CAST(ADD_MONTHS(LAST_START,ROWNUM) AS TIMESTAMP) STARTDATE,
         CAST(ADD_MONTHS(LAST_END,ROWNUM) AS TIMESTAMP) ENDDATE
  FROM   (SELECT MAX(STARTDATE) LAST_START, MAX(ENDDATE) LAST_END, CEIL(MONTHS_BETWEEN(TRANGE.MAX,MAX(ENDDATE))) NUMMON FROM TCYCLE_ACCOUNT, TRANGE GROUP BY TRANGE.MAX)
  WHERE  NUMMON > 0
  CONNECT BY LEVEL <= NUMMON),
tcycle AS (
  SELECT STARTDATE, ENDDATE  FROM TCYCLE_ACCOUNT
  UNION ALL
  SELECT STARTDATE, ENDDATE
  FROM TCYCLE_GAPS
),
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
)
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,
       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, tcycle, alldays
WHERE  vdate between tcycle.STARTDATE AND tcycle.ENDDATE /*  For each hour frame I get the billing period range */
AND    t1.ENDTIMEUTC(+) >= tcycle.STARTDATE AND t1.ENDTIMEUTC(+) < vdate+1 /*  The Billing end time must be inside the range until the computed hour */
group by seq, vdate
order by seq;


70 rows selected.