How to iterate over a date range in PL/SQL select-CodePudding

Hi Thank you for considering to look at my problem, However I am trying to figure out to iterate over a date range in select statement

Basically select statement gives the result if a job is open at certain date. We would like iterate over dates and find count number of job openings on each day.

 declare
   dfrom date;
   dtill date;
   AB date;
   begin
      dfrom := TO_DATE('2022-09-01', 'YYYY-MM-DD');
      dtill := TO_DATE('2022-09-10', 'YYYY-MM-DD');
      AB := dfrom;

    SELECT COUNT(A.HRS_JOB_OPENING_ID) AS openings_cnt
    ,TO_DATE(AB,'YYYY-MM-DD') as Calendar_Date
    FROM WC_HRS_JOB_OPENING_F  A
   JOIN w_int_org_d BU on BU.row_wid = A.Business_unit_wid
  JOIN w_int_org_d DP on DP.row_wid = A.DEPTID_WID
   WHERE 
    ( A.OPEN_DT <= TO_DATE(AB,'YYYY-MM-DD')
    AND ( A.CLOSE_DT IS NULL OR A.CLOSE_DT >= TO_DATE(AB,'YYYY-MM-DD'))
    AND A.STATUS_CODE IN ('010','110')
    AND NOT ( A.STATUS_DT < TO_DATE(AB,'YYYY-MM-DD') AND A.STATUS_CODE IN ('110','120')) 
    )
       group by  TO_DATE(AB,'YYYY-MM-DD') 
    LOOP
       AB := AB   1;
       EXIT WHEN AB < dtill;
          dbms_output.put_line( openings_cnt ||  Calendar_Date );
     END LOOP;
   END;

   Result would like 
   3000 2022-09-01
   3478 2022-09-02
   3895 2022-09-03
   3256 2022-09-04
   3145 2022-09-05

I have tried to create a statement like above, but it gives me error, I am sure I am missing something

CodePudding user response：

You have to put you select statement inside the loop and get the counts in a local variable for each increment of date. Eg.

declare
 dfrom date;
 dtill date;
 AB date;
 lv_opening_cnt number;
begin
  dfrom := TO_DATE('2022-09-01', 'YYYY-MM-DD');
  dtill := TO_DATE('2022-09-10', 'YYYY-MM-DD');
  AB := dfrom;
LOOP
 SELECT COUNT(A.HRS_JOB_OPENING_ID) 
   INTO lv_opening_cnt
 FROM WC_HRS_JOB_OPENING_F  A
 JOIN w_int_org_d BU on BU.row_wid = A.Business_unit_wid
 JOIN w_int_org_d DP on DP.row_wid = A.DEPTID_WID
 WHERE 
 ( A.OPEN_DT <= AB
 AND ( A.CLOSE_DT IS NULL OR A.CLOSE_DT >= AB)
 AND A.STATUS_CODE IN ('010','110')
 AND NOT ( A.STATUS_DT < AB) AND A.STATUS_CODE IN 
 ('110','120')) 
  )
 group by  AB;

  dbms_output.put_line( lv_openings_cnt ||  AB );
  AB := AB   1;
  EXIT WHEN AB > dtill;
END LOOP;
END;