4i.2.306. Top Compartments - Total Costs, between 2020-05-11 and 2020-06-20 (OCI360_REPORTS_COST) 4i.2.306. Top Compartments - Total Costs, between 2020-05-11 and 2020-06-20 (OCI360_REPORTS_COST)
| # | SNAP_ID | BEGIN_TIME | END_TIME | LINE1 | LINE2 | LINE3 | LINE4 | LINE5 | LINE6 | LINE7 | LINE8 | LINE9 | LINE10 | LINE11 | LINE12 | LINE13 | LINE14 | LINE15 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2020-05-11 00:00 | 2020-05-12 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 2 | 2 | 2020-05-12 00:00 | 2020-05-13 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 3 | 3 | 2020-05-13 00:00 | 2020-05-14 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 4 | 4 | 2020-05-14 00:00 | 2020-05-15 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 5 | 5 | 2020-05-15 00:00 | 2020-05-16 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 6 | 6 | 2020-05-16 00:00 | 2020-05-17 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 7 | 7 | 2020-05-17 00:00 | 2020-05-18 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 8 | 8 | 2020-05-18 00:00 | 2020-05-19 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 9 | 9 | 2020-05-19 00:00 | 2020-05-20 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 10 | 10 | 2020-05-20 00:00 | 2020-05-21 00:00 | 0.00 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 11 | 11 | 2020-05-21 00:00 | 2020-05-22 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 12 | 12 | 2020-05-22 00:00 | 2020-05-23 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 13 | 13 | 2020-05-23 00:00 | 2020-05-24 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 14 | 14 | 2020-05-24 00:00 | 2020-05-25 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 15 | 15 | 2020-05-25 00:00 | 2020-05-26 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 16 | 16 | 2020-05-26 00:00 | 2020-05-27 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 17 | 17 | 2020-05-27 00:00 | 2020-05-28 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 18 | 18 | 2020-05-28 00:00 | 2020-05-29 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 19 | 19 | 2020-05-29 00:00 | 2020-05-30 00:00 | 0.89 | 0.26 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 20 | 20 | 2020-05-30 00:00 | 2020-05-31 00:00 | 1.58 | 0.21 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 21 | 21 | 2020-05-31 00:00 | 2020-06-01 00:00 | 2.52 | 0.79 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 22 | 22 | 2020-06-01 00:00 | 2020-06-02 00:00 | 2.52 | 0.79 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 23 | 23 | 2020-06-02 00:00 | 2020-06-03 00:00 | 2.52 | 0.79 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 24 | 24 | 2020-06-03 00:00 | 2020-06-04 00:00 | 1.57 | 0.49 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 25 | 25 | 2020-06-04 00:00 | 2020-06-05 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 26 | 26 | 2020-06-05 00:00 | 2020-06-06 00:00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 27 | 27 | 2020-06-06 00:00 | 2020-06-07 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 28 | 28 | 2020-06-07 00:00 | 2020-06-08 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 29 | 29 | 2020-06-08 00:00 | 2020-06-09 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 30 | 30 | 2020-06-09 00:00 | 2020-06-10 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 31 | 31 | 2020-06-10 00:00 | 2020-06-11 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 32 | 32 | 2020-06-11 00:00 | 2020-06-12 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 33 | 33 | 2020-06-12 00:00 | 2020-06-13 00:00 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| 34 | 34 | 2020-06-13 00:00 | 2020-06-14 00:00 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 35 | 35 | 2020-06-14 00:00 | 2020-06-15 00:00 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 36 | 36 | 2020-06-15 00:00 | 2020-06-16 00:00 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 37 | 37 | 2020-06-16 00:00 | 2020-06-17 00:00 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 38 | 38 | 2020-06-17 00:00 | 2020-06-18 00:00 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 39 | 39 | 2020-06-18 00:00 | 2020-06-19 00:00 | 0.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 40 | 40 | 2020-06-19 00:00 | 2020-06-20 00:00 | 0.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
#: click on a column heading to sort on it
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
lineItem/referenceNo VARCHAR2(4000)
lineItem/tenantId VARCHAR2(4000)
lineItem/intervalUsageStart VARCHAR2(4000)
lineItem/intervalUsageEnd VARCHAR2(4000)
product/service VARCHAR2(4000)
product/compartmentId VARCHAR2(4000)
product/compartmentName VARCHAR2(4000)
product/region VARCHAR2(4000)
product/availabilityDomain VARCHAR2(4000)
product/resourceId VARCHAR2(4000)
usage/billedQuantity VARCHAR2(4000)
usage/billedQuantityOverage VARCHAR2(4000)
cost/subscriptionId VARCHAR2(4000)
cost/productSku VARCHAR2(4000)
product/Description VARCHAR2(4000)
cost/unitPrice VARCHAR2(4000)
cost/unitPriceOverage VARCHAR2(4000)
cost/myCost VARCHAR2(4000)
cost/myCostOverage VARCHAR2(4000)
cost/currencyCode VARCHAR2(4000)
cost/billingUnitReadable VARCHAR2(4000)
cost/overageFlag VARCHAR2(4000)
lineItem/isCorrection VARCHAR2(4000)
lineItem/backreferenceNo VARCHAR2(4000)
tags/Oracle-Tags.CreatedBy VARCHAR2(4000)
tags/Oracle-Tags.CreatedOn VARCHAR2(4000)
tags/orcl-cloud.free-tier-retained VARCHAR2(4000)
WITH t1 AS (
SELECT SUM("cost/myCost") COMPUTEDAMOUNT,
TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"') ENDTIMEUTC,
"product/compartmentId" ITEM_ID
FROM OCI360_REPORTS_COST
/* WHERE */
GROUP BY TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"'), "product/compartmentId"
),
trange as (
select trunc(min(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') min,
trunc(max(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') max
FROM OCI360_REPORTS_COST
),
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(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000229',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line1,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000210',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line2,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.tenancy.oc1..000000000000000000000000000000000000000000000000000000000686',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line3,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000211',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line4,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000212',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line5,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000213',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line6,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000214',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line7,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000215',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line8,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000216',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line9,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000217',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line10,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000218',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line11,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000219',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line12,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000220',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line13,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000221',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line14,
TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000222',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line15
from t1, alldays
where ENDTIMEUTC(+) >= vdate and ENDTIMEUTC(+) < vdate+1
group by seq, vdate
order by seq;
40 rows selected.