oci360 4i.2.310. Top Regions - 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.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
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.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
19 19 2020-05-29 00:00 2020-05-30 00:00 1.14 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
20 20 2020-05-30 00:00 2020-05-31 00:00 1.79 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
21 21 2020-05-31 00:00 2020-06-01 00:00 3.30 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
22 22 2020-06-01 00:00 2020-06-02 00:00 3.30 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
23 23 2020-06-02 00:00 2020-06-03 00:00 3.30 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
24 24 2020-06-03 00:00 2020-06-04 00:00 2.06 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
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.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
28 28 2020-06-07 00:00 2020-06-08 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
29 29 2020-06-08 00:00 2020-06-09 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
30 30 2020-06-09 00:00 2020-06-10 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
31 31 2020-06-10 00:00 2020-06-11 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
32 32 2020-06-11 00:00 2020-06-12 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
33 33 2020-06-12 00:00 2020-06-13 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
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/region" ITEM_ID
  FROM   OCI360_REPORTS_COST
/*   WHERE   */
  GROUP BY TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"'), "product/region"
),
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,'us-ashburn-1',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line1,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line2,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line3,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line4,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line5,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line6,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line7,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line8,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line9,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line10,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line11,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line12,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line13,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line14,
       TO_CHAR(NVL(CEIL(SUM(DECODE(ITEM_ID,'',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.

oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T16:12:46