Filtering data with previous month in plsql-CodePudding

I have a procedure which is used for extracting data and inserting them into one table. My problem is that I want to get previous months data by looking at bill_account_billcycle table's bill_date column. I have already seen so many examples about first and last day of previous month but I am looking for exactly the last month. Because bill date changes for ever customer in my case?

AND ab.bill_date = bab.bill_date;

so if I can look at previous month of bab.bill_date, I think it cant treat everyting. I am adding my procedure's scripts and one row from bill_account_cycle.bill_date for an example I am open to any advices thank you from now.

    create or replace procedure GPU_DATA_EXTRACTOR_TEST is
c_limit   CONSTANT PLS_INTEGER DEFAULT 10000;


CURSOR c1 IS
SELECT DISTINCT intl_prod_id
      FROM apld_bill_rt abr,
           acct_bill ab,
           bill_acct_billcycle bab
      WHERE abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          AND ab.bill_date = bab.bill_date; 
         

TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER;
l_prod_ids   prod_ids_t;
begin

   execute immediate 'truncate table GPU_INV_TEST';

  OPEN c1;
  
 
  
  LOOP
     FETCH c1 BULK COLLECT INTO l_prod_ids LIMIT c_limit;
     
     EXIT WHEN l_prod_ids.COUNT = 0;

    FORALL indx IN 1 .. l_prod_ids.COUNT
    
      INSERT INTO GPU_INV_TEST
         SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba2.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                bill_acct ba2,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id( )
                AND ABR.intl_prod_id = l_prod_ids(indx)
UNION
    SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba1.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                --AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba1.intl_bill_acct_id = bap.intl_bill_acct_id
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id( )
                AND ABR.intl_prod_id = l_prod_ids(indx);

      
    COMMIT;  
    
    END LOOP;
   CLOSE c1;
end;

CodePudding user response：

I am looking for exactly the last month

If "last month" is "previous month", then you'd use values returned by

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select trunc(add_months(sysdate, -1), 'mm') date_from,
  2         trunc(sysdate, 'mm') - 1 as date_to
  3  from dual
  4  /

DATE_FROM  DATE_TO
---------- ----------
01.09.2021 30.09.2021

SQL>

and your query might look like

AND ab.bill_date BETWEEN trunc(add_months(sysdate, -1), 'mm') 
                     AND trunc(sysdate, 'mm') - 1
AND ab.bill_date = bab.bill_date