4g.186. Object: idgimbpbaoa7/oci360_bucket Res: PIC_OBJECT_STORAGE_TIERED between 2020-05-29 and 2020-06-28 (OCI360_REPORTS_USAGE)

#: 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/resource                                                                             VARCHAR2(4000)
 product/compartmentId                                                                        VARCHAR2(4000)
 product/compartmentName                                                                      VARCHAR2(4000)
 product/region                                                                               VARCHAR2(4000)
 product/availabilityDomain                                                                   VARCHAR2(4000)
 product/resourceId                                                                           VARCHAR2(4000)
 usage/consumedQuantity                                                                       VARCHAR2(4000)
 usage/billedQuantity                                                                         VARCHAR2(4000)
 usage/consumedQuantityUnits                                                                  VARCHAR2(4000)
 usage/consumedQuantityMeasure                                                                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)
 tags/backup                                                                                  VARCHAR2(4000)


WITH t1 AS (
  SELECT SUM("usage/consumedQuantity") CONSUMED_AMOUNT,
         SUM("usage/billedQuantity") BILLED_AMOUNT,
         TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"') ENDTIMEUTC
  FROM   OCI360_REPORTS_USAGE
  WHERE  "product/resource" = 'PIC_OBJECT_STORAGE_TIERED' AND "product/resourceId" = 'idgimbpbaoa7/oci360_bucket'
  GROUP BY TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')
),
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_USAGE
),
allhours as ( /*  Will generate all hours between Min and Max Start Time */
  SELECT trange.min + (rownum - 1)/24 vdate,
         rownum seq
  FROM   trange
  WHERE  trange.min + (rownum - 1)/24 <= trange.max - 1/24  /*  Skip last entry as may be incomplete. */
  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(NVL(SUM(CONSUMED_AMOUNT),0)) line1,
       TO_CHAR(NVL(SUM(BILLED_AMOUNT),0))   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, allhours
where  ENDTIMEUTC(+) >= vdate and ENDTIMEUTC(+)  < vdate+1/24
group by seq, vdate
order by seq;


718 rows selected.