5e.4.560. Service Group: ORCL:OCIService=Block Storage, between 2020-04-19 and 2020-06-29 (OCI360_USAGECOSTS_TAGGED_DAILY) 5e.4.560. Service Group: ORCL:OCIService=Block Storage, between 2020-04-19 and 2020-06-29 (OCI360_USAGECOSTS_TAGGED_DAILY)
| # | SNAP_ID | BEGIN_TIME | END_TIME | LINE1 |
|---|---|---|---|---|
| 1 | 1 | 2020-04-19 00:00 | 2020-04-20 00:00 | 0.00 |
| 2 | 2 | 2020-04-20 00:00 | 2020-04-21 00:00 | 0.00 |
| 3 | 3 | 2020-04-21 00:00 | 2020-04-22 00:00 | 0.00 |
| 4 | 4 | 2020-04-22 00:00 | 2020-04-23 00:00 | 0.00 |
| 5 | 5 | 2020-04-23 00:00 | 2020-04-24 00:00 | 0.00 |
| 6 | 6 | 2020-04-24 00:00 | 2020-04-25 00:00 | 0.00 |
| 7 | 7 | 2020-04-25 00:00 | 2020-04-26 00:00 | 0.00 |
| 8 | 8 | 2020-04-26 00:00 | 2020-04-27 00:00 | 0.01 |
| 9 | 9 | 2020-04-27 00:00 | 2020-04-28 00:00 | 0.01 |
| 10 | 10 | 2020-04-28 00:00 | 2020-04-29 00:00 | 0.01 |
| 11 | 11 | 2020-04-29 00:00 | 2020-04-30 00:00 | 0.01 |
| 12 | 12 | 2020-04-30 00:00 | 2020-05-01 00:00 | 0.01 |
| 13 | 13 | 2020-05-01 00:00 | 2020-05-02 00:00 | 0.01 |
| 14 | 14 | 2020-05-02 00:00 | 2020-05-03 00:00 | 0.01 |
| 15 | 15 | 2020-05-03 00:00 | 2020-05-04 00:00 | 0.01 |
| 16 | 16 | 2020-05-04 00:00 | 2020-05-05 00:00 | 0.01 |
| 17 | 17 | 2020-05-05 00:00 | 2020-05-06 00:00 | 0.01 |
| 18 | 18 | 2020-05-06 00:00 | 2020-05-07 00:00 | 0.01 |
| 19 | 19 | 2020-05-07 00:00 | 2020-05-08 00:00 | 0.01 |
| 20 | 20 | 2020-05-08 00:00 | 2020-05-09 00:00 | 0.01 |
| 21 | 21 | 2020-05-09 00:00 | 2020-05-10 00:00 | 0.01 |
| 22 | 22 | 2020-05-10 00:00 | 2020-05-11 00:00 | 0.01 |
| 23 | 23 | 2020-05-11 00:00 | 2020-05-12 00:00 | 0.01 |
| 24 | 24 | 2020-05-12 00:00 | 2020-05-13 00:00 | 0.01 |
| 25 | 25 | 2020-05-13 00:00 | 2020-05-14 00:00 | 0.01 |
| 26 | 26 | 2020-05-14 00:00 | 2020-05-15 00:00 | 0.01 |
| 27 | 27 | 2020-05-15 00:00 | 2020-05-16 00:00 | 0.01 |
| 28 | 28 | 2020-05-16 00:00 | 2020-05-17 00:00 | 0.01 |
| 29 | 29 | 2020-05-17 00:00 | 2020-05-18 00:00 | 0.01 |
| 30 | 30 | 2020-05-18 00:00 | 2020-05-19 00:00 | 0.01 |
| 31 | 31 | 2020-05-19 00:00 | 2020-05-20 00:00 | 0.01 |
| 32 | 32 | 2020-05-20 00:00 | 2020-05-21 00:00 | 0.01 |
| 33 | 33 | 2020-05-21 00:00 | 2020-05-22 00:00 | 0.01 |
| 34 | 34 | 2020-05-22 00:00 | 2020-05-23 00:00 | 0.01 |
| 35 | 35 | 2020-05-23 00:00 | 2020-05-24 00:00 | 0.01 |
| 36 | 36 | 2020-05-24 00:00 | 2020-05-25 00:00 | 0.01 |
| 37 | 37 | 2020-05-25 00:00 | 2020-05-26 00:00 | 0.01 |
| 38 | 38 | 2020-05-26 00:00 | 2020-05-27 00:00 | 0.01 |
| 39 | 39 | 2020-05-27 00:00 | 2020-05-28 00:00 | 0.01 |
| 40 | 40 | 2020-05-28 00:00 | 2020-05-29 00:00 | 0.01 |
| 41 | 41 | 2020-05-29 00:00 | 2020-05-30 00:00 | 0.01 |
| 42 | 42 | 2020-05-30 00:00 | 2020-05-31 00:00 | 0.04 |
| 43 | 43 | 2020-05-31 00:00 | 2020-06-01 00:00 | 0.11 |
| 44 | 44 | 2020-06-01 00:00 | 2020-06-02 00:00 | 0.17 |
| 45 | 45 | 2020-06-02 00:00 | 2020-06-03 00:00 | 0.24 |
| 46 | 46 | 2020-06-03 00:00 | 2020-06-04 00:00 | 0.30 |
| 47 | 47 | 2020-06-04 00:00 | 2020-06-05 00:00 | 0.34 |
| 48 | 48 | 2020-06-05 00:00 | 2020-06-06 00:00 | 0.34 |
| 49 | 49 | 2020-06-06 00:00 | 2020-06-07 00:00 | 0.34 |
| 50 | 50 | 2020-06-07 00:00 | 2020-06-08 00:00 | 0.34 |
| 51 | 51 | 2020-06-08 00:00 | 2020-06-09 00:00 | 0.34 |
| 52 | 52 | 2020-06-09 00:00 | 2020-06-10 00:00 | 0.34 |
| 53 | 53 | 2020-06-10 00:00 | 2020-06-11 00:00 | 0.34 |
| 54 | 54 | 2020-06-11 00:00 | 2020-06-12 00:00 | 0.34 |
| 55 | 55 | 2020-06-12 00:00 | 2020-06-13 00:00 | 0.34 |
| 56 | 56 | 2020-06-13 00:00 | 2020-06-14 00:00 | 0.34 |
| 57 | 57 | 2020-06-14 00:00 | 2020-06-15 00:00 | 0.34 |
| 58 | 58 | 2020-06-15 00:00 | 2020-06-16 00:00 | 0.34 |
| 59 | 59 | 2020-06-16 00:00 | 2020-06-17 00:00 | 0.34 |
| 60 | 60 | 2020-06-17 00:00 | 2020-06-18 00:00 | 0.34 |
| 61 | 61 | 2020-06-18 00:00 | 2020-06-19 00:00 | 0.34 |
| 62 | 62 | 2020-06-19 00:00 | 2020-06-20 00:00 | 0.34 |
| 63 | 63 | 2020-06-20 00:00 | 2020-06-21 00:00 | 0.34 |
| 64 | 64 | 2020-06-21 00:00 | 2020-06-22 00:00 | 0.34 |
| 65 | 65 | 2020-06-22 00:00 | 2020-06-23 00:00 | 0.34 |
| 66 | 66 | 2020-06-23 00:00 | 2020-06-24 00:00 | 0.34 |
| 67 | 67 | 2020-06-24 00:00 | 2020-06-25 00:00 | 0.34 |
| 68 | 68 | 2020-06-25 00:00 | 2020-06-26 00:00 | 0.34 |
| 69 | 69 | 2020-06-26 00:00 | 2020-06-27 00:00 | 0.34 |
| 70 | 70 | 2020-06-27 00:00 | 2020-06-28 00:00 | 0.35 |
| 71 | 71 | 2020-06-28 00:00 | 2020-06-29 00:00 | 0.40 |
#: click on a column heading to sort on it
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TAG VARCHAR2(128)
CURRENCY VARCHAR2(4)
ENDTIMEUTC VARCHAR2(32)
SERVICENAME VARCHAR2(32)
GSIPRODUCTID VARCHAR2(8)
RESOURCENAME VARCHAR2(64)
STARTTIMEUTC VARCHAR2(32)
SUBSCRIPTIONID VARCHAR2(8)
SUBSCRIPTIONTYPE VARCHAR2(16)
SERVICEENTITLEMENTID VARCHAR2(16)
COSTS$UNITPRICE NUMBER
COSTS$OVERAGESFLAG VARCHAR2(1)
COSTS$COMPUTEDAMOUNT NUMBER
COSTS$COMPUTEDQUANTITY NUMBER
WITH t1 AS (
SELECT SUM(COSTS$COMPUTEDAMOUNT) COMPUTEDAMOUNT,
TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') ENDTIMEUTC
FROM OCI360_USAGECOSTS_TAGGED_DAILY
WHERE TAG = 'ORCL:OCIService=Block Storage'
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_TAGGED_DAILY
),
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;
71 rows selected.