Is ther any clear way of doing the following in one SQL (PL) code? :)

What I have in the tables is the following:

CONTACT_TBL (HEADER)

CONTRACT_ACTIVTY (DETAIL)

What we need is to populate for each row:

1. BEGIN_DATE

• For SEQNUM #1 - BEGIN_DATE is always CONTACT_TBL.BEGIN_DATE (in this case 13-MAY-15, a 3 year span)
• For SEQNUM #2 to N - BEGIN_DATE is 1 day more than previous row's END_DATE (in this case 12-MAY-19 1 = 13-MAY-19, all 5 year spans)

2. Mark the row 'Active' if the SYSDATE is between BEGIN_DATE and END_DATE (in this case it's Row #2)

CodePudding user response：

N.B. This answer assumes the db is Oracle

This can be done by using LAG to find the date from the previous row.

LAG can accept 3 parameters, the first of which is the column whose value you want to find from the previous row, the second is how many rows to go back each time (default is 1), and the third is what to display if there is no previous row found.

In your case, you want to default to the begin_date from the contact_tbl if there is no previous row to the first row in the contract_activity table, so you need to join the two tables as well, like so:

WITH   contact_tbl AS (SELECT 123 contract_id, to_date('13/05/2016', 'dd/mm/yyyy') begin_date, to_date('12/05/2034', 'dd/mm/yyyy') end_date, 100 tot_amount FROM dual UNION ALL
                       SELECT 456 contract_id, to_date('13/05/2016', 'dd/mm/yyyy') begin_date, to_date('12/05/2034', 'dd/mm/yyyy') end_date, 100 tot_amount FROM dual UNION ALL
                       SELECT 789 contract_id, to_date('13/05/2016', 'dd/mm/yyyy') begin_date, to_date('12/05/2034', 'dd/mm/yyyy') end_date, 100 tot_amount FROM dual),
 contract_activity AS (SELECT 123 contract_id, 1 seqnum, to_date('12/05/2019', 'dd/mm/yyyy') end_date, 25 amount, '25 - Initial Expiration' comments FROM dual UNION ALL
                       SELECT 123 contract_id, 2 seqnum, to_date('12/05/2024', 'dd/mm/yyyy') end_date, 25 amount, '25' comments FROM dual UNION ALL
                       SELECT 123 contract_id, 3 seqnum, to_date('12/05/2029', 'dd/mm/yyyy') end_date, 25 amount, '25' comments FROM dual UNION ALL
                       SELECT 123 contract_id, 4 seqnum, to_date('12/05/2034', 'dd/mm/yyyy') end_date, 25 amount, '25 - End of Contract' comments FROM dual),
-- end of creating subqueries to mimic your tables
  contract_details AS (SELECT ca.contract_id,
                              ca.seqnum,
                              LAG(ca.end_date   1, 1, c.begin_date) OVER (PARTITION BY ca.contract_id ORDER BY seqnum) begin_date,
                              ca.end_date,
                              ca.amount
                       FROM   contact_tbl c
                              INNER JOIN contract_activity ca ON c.contract_id = ca.contract_id)
SELECT contract_id,
       seqnum,
       begin_date,
       end_date,
       amount,
       CASE WHEN TRUNC(SYSDATE) BETWEEN begin_date AND end_date THEN 'Active' END status
FROM   contract_details;

CONTRACT_ID     SEQNUM BEGIN_DATE  END_DATE        AMOUNT STATUS
----------- ---------- ----------- ----------- ---------- ------
        123          1 13/05/2016  12/05/2019          25 
        123          2 13/05/2019  12/05/2024          25 Active
        123          3 13/05/2024  12/05/2029          25 
        123          4 13/05/2029  12/05/2034          25 

Your status column is a simple case expression, but since it refers to the calculated begin_date, rather than repeating the calculation, I did the begin_date calculation in one subquery, and then referenced that in the outer query.

Note how I used TRUNC(sysdate) instead of just sysdate in the case expression for the status - DATEs in Oracle have a time component (which defaults to midnight if you don't specify the time), so if you ran the query any time between 12/05/2024 00:00:01 and 12/05/2024 23:59:59, your query wouldn't return a status of Active for the second row if you used sysdate. You need to explicitly truncate sysdate to midnight in order for the comparison to work.

CodePudding user response：

Hope below snippet serve the purpose.

 DECLARE

   CURSOR cur_contracts IS
   SELECT
     *
   FROM
     CONTACT_TBL
   ORDER BY
     CONTRACT_ID;

   CURSOR cur_contracts_act(contact_id NUMBER) IS
   SELECT
     *
   FROM
     CONTRACT_ACTIVTY
   ORDER BY
     CONTRACT_ID,
     SEQNUM;
     
   l_ca_begin_date DATE;
   l_ca_status VARCHAR2(10);
   
BEGIN
   FOR i IN cur_contracts
   LOOP
      contract_activity_seq := 0;
      FOR j IN cur_contracts_act (i.CONTRACT_ID)
      LOOP
         contract_activity_seq := contract_activity_seq   1;
         IF (contract_activity_seq = 1) THEN 
            l_ca_begin_date := i.begin_date;
         ELSE 
            l_ca_begin_date := i.end_date   1;
         END IF;
         IF SYSDATE BETWEEN l_ca_begin_date AND j.end_date THEN 
            l_ca_status := 'Active';
         ELSE 
            l_ca_status := NULL;
         END IF;
         
         UPDATE CONTRACT_ACTIVTY
         SET BEGIN_DATE = l_ca_begin_date,
             status = l_ca_status
         WHERE CURRENT OF cur_contracts_act;
         END LOOP;
   END LOOP;

   COMMIT;
EXCEPTION
  WHEN OTHERS THEN raise_application_error(
    -20001,
    'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM
  );
END;
/