sql query to project the future volume based on historical data in oracle

Question

I have written a query to split the count into multiple rows. This is just a sample data so I have split into 2 rows as mentioned in the OUTPUT. Now the requirement is get the count of 7 days prior to first row , 14 days prior to 2nd row , 21 days --3rd row and so on based on number of split counts. Future date is 180 days after the crtd date. please advise and let me know if you need more details on this . Thanks in advance

SAMPLE DATA

CREATE TABLE T_DAT  (   "CRT_DT" DATE );

Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR')); commit;

query to be modified to get the expected output below

 with data_set as 
     ( select d.crt_dt as crt_dt,
    trunc(d.crt_dt) + 180 as future_dt,
    count(*) approx_cnt
    from  t_dat d
    where    d.crt_dt<=sysdate and
             d.crt_dt >= (trunc(sysdate) - 180)       
    group by d.crt_dt 
    having trunc(d.crt_dt) + 180 >= trunc(sysdate)-9 and  count(*)>10),
     vol as (select level  from dual connect by level <= (select approx_cnt/6 from data_set))
     select crt_dt, future_dt , approx_cnt 
      from data_set d ,vol;


**OUTPUT** 

CRT_DT       FUTURE_DT  APPROX_CNT
04-MAR-23   31-AUG-23   15
04-MAR-23   31-AUG-23   15  

 EXPECTED OUTPUT 

CRT_DT      FUTURE_DT  APPROX_CNT
25-FEB-23   24-AUG-23   7
18-FEB-23   17-AUG-23   10

Answer 1

Hope this helps. I have added comments to explain what I am doing.

WITH 
--first determine from which date to begin and till which date to end.
min_max_fetch AS (
    SELECT
        MIN(td.crt_dt) AS min_crt,
        MAX(td.crt_dt) AS max_crt
    FROM
        t_dat td
    WHERE
            td.crt_dt <= sysdate
        AND td.crt_dt >= ( trunc(sysdate) - 180 )
)
--now fetch the date range for every row based on above
, weekly_fetch AS (
    SELECT
        mf.max_crt - ( level * 7 )         AS from_date,
        mf.max_crt - ( ( level - 1 ) * 7 ) AS to_date
    FROM
        min_max_fetch mf
    CONNECT BY
        mf.max_crt - ( level * 7 ) >= mf.min_crt
)
--now fetch count of data that lies in the above date range
SELECT
    wf.from_date,
    wf.from_date + 180 AS future_dt,
    (
        SELECT
            COUNT(*)
        FROM
            t_dat td
        WHERE
            td.crt_dt BETWEEN wf.from_date AND wf.to_date - 1
    )                  AS approx_cnt
FROM
    weekly_fetch wf
ORDER BY
    wf.from_date DESC;