4i.3.337. Service: DATABASE, between 2020-05-11 and 2020-06-20 (OCI360_REPORTS_COST)

 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 ( /*  Cost of the resource group by ENDTIME */
  SELECT SUM("cost/myCost") COMPUTEDAMOUNT,
         TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"') ENDTIMEUTC
  FROM   OCI360_REPORTS_COST
  WHERE  "product/service" = 'DATABASE'
  GROUP BY TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')
),
trange as ( /*  Min and Max range of the costs */
  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
),
tcycle_account AS (
  SELECT /*+ materialize */
         TO_TIMESTAMP(add_months(trunc(trange.min,'mm'),ROWNUM-1)) STARTDATE,
         TO_TIMESTAMP(add_months(trunc(trange.min,'mm'),ROWNUM)) - numToDSInterval( 1, 'second' ) ENDDATE,
         ROWNUM seq
  FROM   trange
  CONNECT BY LEVEL <= ceil(months_between(trange.max,trange.min)) + 1
  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 + 3
),
tcycle AS (
  SELECT STARTDATE, ENDDATE, SEQ FROM TCYCLE_ACCOUNT
  UNION ALL
  SELECT STARTDATE, ENDDATE, RANK() OVER (ORDER BY ENDDATE ASC) + LAST SEQ
  FROM TCYCLE_GAPS, (SELECT MAX(SEQ) LAST FROM TCYCLE_ACCOUNT)
),
result AS (
  select seq snap_id,
         TO_CHAR(ENDDATE, 'YYYY-MM-DD') bar_name,
         TO_CHAR(STARTDATE, 'YYYY-MM-DD HH24:MI') || ' - ' || TO_CHAR(ENDDATE, 'YYYY-MM-DD HH24:MI') bar_desc,
         TO_CHAR(NVL(CEIL(SUM(COMPUTEDAMOUNT)*100)/100,0),'99999990D00') bar_value,
         CASE WHEN STARTDATE > min AND ENDDATE < max THEN 'Y' ELSE 'N' END completed /*  Check for incomplete periods */
  from   t1, tcycle, trange
  WHERE  t1.ENDTIMEUTC(+) >= tcycle.STARTDATE AND t1.ENDTIMEUTC(+) < tcycle.ENDDATE /*  The Billing end time must be inside the range until the computed hour */
    AND  (tcycle.STARTDATE between trange.min and trange.max OR tcycle.ENDDATE between trange.min and trange.max)
  group by seq, tcycle.STARTDATE, tcycle.ENDDATE, min, max
  order by seq
),
statistics as (
  select REGR_SLOPE(bar_value,snap_id) slope,
         REGR_INTERCEPT(bar_value,snap_id) intercept
  from   result
  where  completed = 'Y' /*  Don't consider incomplete periods for calc */
),
result_next3 as (
  select seq snap_id,
         TO_CHAR(ENDDATE, 'YYYY-MM-DD') bar_name,
         TO_CHAR(STARTDATE, 'YYYY-MM-DD HH24:MI') || ' - ' || TO_CHAR(ENDDATE, 'YYYY-MM-DD HH24:MI') bar_desc,
         TO_CHAR(NVL(CEIL((slope*seq+intercept)*100)/100,0),'99999990D00') bar_value /*  Linear Regression calculate. */
  from   tcycle, trange, statistics
  WHERE  (tcycle.STARTDATE >= trange.max OR tcycle.ENDDATE >= trange.max)
    and  slope is not null and intercept is not null
  group by seq, tcycle.STARTDATE, tcycle.ENDDATE, slope, intercept
  order by seq
),
result_final as (
  select snap_id,
         bar_name,
         bar_value,
         'USD ' || bar_value bar_desc,
         'opacity: 0.6' colour
  from   result
  where  completed = 'Y'
  union all
  select snap_id,
         bar_name,
         bar_value,
         'USD ' || bar_value bar_desc,
         'color: #703593; opacity: 0.6' colour
  from   result_next3
  where  rownum <= 3
)
select bar_name, bar_value, colour, bar_desc
from   result_final
order by snap_id;


0 rows selected.