1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Is it possible to derive a recurring date based upon a single date?

Discussion in 'SQL PL/SQL' started by isoman4not5, Nov 30, 2015.

  1. isoman4not5

    isoman4not5 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Margate, NJ
    I'm trying to set up a reminder. It needs to remind users of a reassessment date that occurs every 90 days based upon the first contact (enrollment_date) with a potential client. But I only need to show the closest upcoming date. So if a client's next closest assessment date is 12-12-15 it would show that date until 12-13-15 when it would be begin to show 3-11-16. Is that possible? Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, to answer your question.
     
  3. isoman4not5

    isoman4not5 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Margate, NJ
    Ok, how do i do that?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That's the question you should have asked. You can do it with PL/SQL or with a 'straight' SQL query:

    Code (SQL):
    SQL> CREATE TABLE enroll (
      2  enroll_dt       DATE NOT NULL,
      3  enrollee        varchar2(40),
      4  enroll_typ NUMBER,
      5  is_curr         varchar2(3));

    TABLE created.

    SQL>
    SQL> INSERT INTO enroll
      2  VALUES(sysdate -43, 'Blerpie', 4, NULL);

    1 ROW created.

    SQL>
    SQL> INSERT INTO enroll
      2  VALUES(sysdate -17, 'Blerpae', 4, NULL);

    1 ROW created.

    SQL>
    SQL> INSERT INTO enroll
      2  VALUES(sysdate -3, 'Blerpoe', 4, NULL);

    1 ROW created.

    SQL>
    SQL> INSERT INTO enroll
      2  VALUES(sysdate -181, 'Blerpue', 4, NULL);

    1 ROW created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> DECLARE
      2          cursor get_enroll_info (p_enrollee IN varchar2) IS
      3          SELECT enroll_dt + 90
      4          FROM enroll
      5          WHERE enrollee = p_enrollee;
      6
      7          v_procdt        DATE;
      8  BEGIN
      9          OPEN get_enroll_info('Blerpie');
    10          fetch get_enroll_info INTO v_procdt;
    11          close get_enroll_info;
    12
    13          IF v_procdt > sysdate THEN
    14                  dbms_output.put_line('Reassessment date: '||v_procdt);
    15          ELSE
    16                  dbms_output.put_line('Reassessment date: '||v_procdt + 90);
    17          END IF;
    18
    19  END;
    20  /
    Reassessment DATE: 18-JAN-16

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SELECT enrollee, enroll_dt, CASE WHEN enroll_dt+90 >= sysdate THEN enroll_dt+90 ELSE enroll_dt+180 END reassessment
      2  FROM enroll;

    ENROLLEE                                 ENROLL_DT REASSESSM
    ---------------------------------------- --------- ---------
    Blerpie                                  20-OCT-15 18-JAN-16
    Blerpae                                  15-NOV-15 13-FEB-16
    Blerpoe                                  29-NOV-15 27-FEB-16
    Blerpue                                  04-JUN-15 01-DEC-15

    SQL>
     
    Ask the correct question ...