So I am quite new to SQL so please be gentle.
I am using Oracle Apex to generate a bar chart showing the amount of storage used in a particular database over the last 12 months. In this chart I am trying to add an additional feature where the chart will show (via a line) how much storage will likely be used on a fortnightly basis over the course of the next three months.
I have created a PL/SQL function (GET_PREDICTED_VALUE) which generates the anticipated value I require, but I am having trouble using that value to show the predicted trend. If the generated anticipated value is positive, that’s how much more storage is needed. If the anticipated value is negative, that’s how much less storage is needed (the arguments sent into this function are how many days ago do I want to draw the ‘storage used’ values. 30 will be 30 days ago, 150 is 150 days ago etc).
What I would like to do is use this anticipated value and add it to the last ‘current storage used’ value recorded in the database. I would like the anticipated value to be continually added so that a future trend can be observed.
For example, if the last ‘current storage used’ value saved in the database is 50, and the anticipated value is 3, then the value for the immediate future date should be 53. And the next future value should be 56, and the next should be 59, and so on up to three months ahead.
This is the code I have so far, and it works insofar that the chart I need does get generated and the trend line runs across the previous year and then the future three months. But for the future three months, I am only able to get the trend line to represent the anticipated value, not the value continually added E.g again, if the anticipated value is 3, my trend line shows 3 across all future dates.
WITH src AS ( SELECT GET_PREDICTED_VALUE(1, 30, 60, 90, 120, 150, 180) AS predictedValue FROM DUAL),mydays as ( select level mylevel, sysdate + level as futureDate from dual connect by level <= 90 ),future_values as ( SELECT MAX(src.predictedValue) AS "ASM Used", TO_CHAR(TRUNC(futureDate,'IW') ,'DD/MM/YY') AS "Capture Date", TRUNC(futureDate,'IW') AS capture_date_order FROM mydays CROSS JOIN src GROUP BY TRUNC(futureDate,'IW') ORDER BY TRUNC(futureDate,'IW') ),SRC_back AS ( SELECT TRUNC(capture_date,'IW') AS week, database_name, MAX((os_usable_total_mb-os_usable_free_mb)) AS ASMUsed FROM tablespace_used WHERE capture_date >= (SYSDATE - 360) GROUP BY TRUNC(capture_date,'IW'), database_name, tablespace_name),back_values as ( SELECT /*+ parallel(a, 8) */ TO_CHAR(week,'DD/MM/YY') AS "Capture Date", week AS capture_date_order, ROUND( MAX(ASMUsed/1024/1024 ) ) AS "ASM Used" FROM src_back a WHERE week >= (SYSDATE - 360) GROUP BY TO_CHAR(week,'DD/MM/YY'), week ORDER BY 2 ) select "Capture Date", capture_date_order, "ASM Used" from back_valuesunion select "Capture Date", capture_date_order, "ASM Used" from future_valuesorder by 2
I hope my explanation is clear, and if anyone could let me know what I have to do to get my trend line running the way I need?
(This is an image of how my chart looks so far):
