Stored procedure for sending emails at specified times after license expiration

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
3
down vote

favorite
1












I need to send email notifications for expired licenses:



  • One Day Before/After Expiration

  • Two Days Before/After Expiration

  • Three Days Before/After Expiration

  • One Week Before/After Expiration

  • Two Weeks Before/After Expiration

  • One Month Before/After Expiration

  • Two Months Before/After Expiration

  • Three Months Before/After Expiration

  • At the time of Expiration

This is the Stored procedure to fetch expired licenses.



DELIMITER $$

DROP PROCEDURE IF EXISTS `licensetrack`$$

CREATE PROCEDURE `licensetrack`()

BEGIN

 DECLARE varOneDay INT;
 DECLARE varTwoDay INT;
 DECLARE varThreeDay INT;
 DECLARE varOneWeek INT;
 DECLARE varTwoWeek INT;
 DECLARE varOneMonth INT;
 DECLARE varTwoMonth INT;
 DECLARE varThreeMonth INT;
 DECLARE varAtTheTime INT;

 DROP TEMPORARY TABLE IF EXISTS templicense;

 CREATE TEMPORARY TABLE IF NOT EXISTS templicense(
 TYPE VARCHAR(50) NOT NULL,
 entityID BIGINT NOT NULL,
 expirationDate DATE,
 beforeOneDay DATE,
 afterOneDay DATE,
 beforeTwoDay DATE,
 afterTwoDay DATE,
 beforeThreeDay DATE,
 afterThreeDay DATE,
 beforeOneWeek DATE,
 afterOneWeek DATE,
 beforeTwoWeek DATE,
 afterTwoWeek DATE,
 beforeOneMonth DATE,
 afterOneMonth DATE,
 beforeTwoMonth DATE,
 afterTwoMonth DATE,
 beforeThreeMonth DATE,
 afterThreeMonth DATE,
 atTheTime DATE

 ) ;


 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
 SELECT 'Organ' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
 FROM organizationlicenses orglic;


 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
 SELECT 'Location' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
 FROM locationlicenses loclic;


 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
 SELECT 'EmpLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
 FROM employeelicenses emplic;


 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
 SELECT 'EmpDrivingLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
 DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
 DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
 DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
 DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
 DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
 DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
 DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
 DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
 DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
 FROM employeedriving drilic;

 INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
 afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
 SELECT 'EmpImgLicense' AS TYPE, ID AS entityID, expiredDate AS expirationDate,
 DATE_SUB(expiredDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expiredDate, INTERVAL -1 DAY) afterOneDay,
 DATE_SUB(expiredDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expiredDate, INTERVAL -2 DAY) afterTwoDay,
 DATE_SUB(expiredDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expiredDate, INTERVAL -3 DAY) afterThreeDay,
 DATE_SUB(expiredDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expiredDate, INTERVAL -1 WEEK) afterOneWeek,
 DATE_SUB(expiredDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expiredDate, INTERVAL -2 WEEK) afterTwoWeek,
 DATE_SUB(expiredDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expiredDate, INTERVAL -1 MONTH) afterOneMonth,
 DATE_SUB(expiredDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expiredDate, INTERVAL -2 MONTH) afterTwoMonth,
 DATE_SUB(expiredDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expiredDate, INTERVAL -3 MONTH) afterThreeMonth,
 DATE_SUB(expiredDate, INTERVAL 0 DAY) atTheTime 
 FROM employeeimages empimglic;



 select oneDay,twoDay,threeDay,oneWeek,
 twoWeek,oneMonth , twoMonth,threeMonth ,atTheTime 

 into varOneDay,varTwoDay,varThreeDay,varOneWeek,varTwoWeek,varOneMonth,varTwoMonth,
 varThreeMonth,varAtTheTime from licensetrackinterval;

 if varOneDay = 0 then
 update templicense set beforeOneDay= null ,afterOneDay= NULL;
 end if;

 IF varTwoDay = 0 THEN
 UPDATE templicense SET beforeTwoDay= NULL,afterTwoDay= NULL;
 END IF;

 IF varThreeDay = 0 THEN
 UPDATE templicense SET beforeThreeDay= NULL, afterThreeDay= NULL;
 END IF;

 IF varOneWeek = 0 THEN
 UPDATE templicense SET beforeOneWeek= NULL, afterOneWeek= NULL;
 END IF;

 IF varTwoWeek= 0 THEN
 UPDATE templicense SET beforeTwoWeek= NULL,afterTwoWeek= NULL;
 END IF;

 IF varOneMonth = 0 THEN
 UPDATE templicense SET beforeOneMonth= NULL,afterOneMonth= NULL;
 END IF;


 IF varTwoMonth = 0 THEN
 UPDATE templicense SET beforeTwoMonth= NULL,afterTwoMonth= NULL;
 END IF;

 IF varThreeMonth = 0 THEN
 UPDATE templicense SET beforeThreeMonth= NULL,afterThreeMonth= NULL;
 END IF;

 IF varAtTheTime = 0 THEN
 UPDATE templicense SET atTheTime= NULL;
 END IF;

 select * from templicense ;


 END$$

DELIMITER ;



sql mysql datetime database stored-procedure




share|improve this question



























    up vote
    3
    down vote

    favorite
    1












    I need to send email notifications for expired licenses:



    • One Day Before/After Expiration

    • Two Days Before/After Expiration

    • Three Days Before/After Expiration

    • One Week Before/After Expiration

    • Two Weeks Before/After Expiration

    • One Month Before/After Expiration

    • Two Months Before/After Expiration

    • Three Months Before/After Expiration

    • At the time of Expiration

    This is the Stored procedure to fetch expired licenses.



    DELIMITER $$

    DROP PROCEDURE IF EXISTS `licensetrack`$$

    CREATE PROCEDURE `licensetrack`()

    BEGIN

     DECLARE varOneDay INT;
     DECLARE varTwoDay INT;
     DECLARE varThreeDay INT;
     DECLARE varOneWeek INT;
     DECLARE varTwoWeek INT;
     DECLARE varOneMonth INT;
     DECLARE varTwoMonth INT;
     DECLARE varThreeMonth INT;
     DECLARE varAtTheTime INT;

     DROP TEMPORARY TABLE IF EXISTS templicense;

     CREATE TEMPORARY TABLE IF NOT EXISTS templicense(
     TYPE VARCHAR(50) NOT NULL,
     entityID BIGINT NOT NULL,
     expirationDate DATE,
     beforeOneDay DATE,
     afterOneDay DATE,
     beforeTwoDay DATE,
     afterTwoDay DATE,
     beforeThreeDay DATE,
     afterThreeDay DATE,
     beforeOneWeek DATE,
     afterOneWeek DATE,
     beforeTwoWeek DATE,
     afterTwoWeek DATE,
     beforeOneMonth DATE,
     afterOneMonth DATE,
     beforeTwoMonth DATE,
     afterTwoMonth DATE,
     beforeThreeMonth DATE,
     afterThreeMonth DATE,
     atTheTime DATE

     ) ;


     INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
     afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
     SELECT 'Organ' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
     DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
     DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
     DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
     DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
     DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
     DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
     DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
     DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
     DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
     FROM organizationlicenses orglic;


     INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
     afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
     SELECT 'Location' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
     DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
     DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
     DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
     DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
     DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
     DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
     DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
     DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
     DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
     FROM locationlicenses loclic;


     INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
     afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
     SELECT 'EmpLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
     DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
     DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
     DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
     DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
     DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
     DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
     DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
     DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
     DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
     FROM employeelicenses emplic;


     INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
     afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
     SELECT 'EmpDrivingLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
     DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
     DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
     DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
     DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
     DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
     DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
     DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
     DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
     DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
     FROM employeedriving drilic;

     INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
     afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
     SELECT 'EmpImgLicense' AS TYPE, ID AS entityID, expiredDate AS expirationDate,
     DATE_SUB(expiredDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expiredDate, INTERVAL -1 DAY) afterOneDay,
     DATE_SUB(expiredDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expiredDate, INTERVAL -2 DAY) afterTwoDay,
     DATE_SUB(expiredDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expiredDate, INTERVAL -3 DAY) afterThreeDay,
     DATE_SUB(expiredDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expiredDate, INTERVAL -1 WEEK) afterOneWeek,
     DATE_SUB(expiredDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expiredDate, INTERVAL -2 WEEK) afterTwoWeek,
     DATE_SUB(expiredDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expiredDate, INTERVAL -1 MONTH) afterOneMonth,
     DATE_SUB(expiredDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expiredDate, INTERVAL -2 MONTH) afterTwoMonth,
     DATE_SUB(expiredDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expiredDate, INTERVAL -3 MONTH) afterThreeMonth,
     DATE_SUB(expiredDate, INTERVAL 0 DAY) atTheTime 
     FROM employeeimages empimglic;



     select oneDay,twoDay,threeDay,oneWeek,
     twoWeek,oneMonth , twoMonth,threeMonth ,atTheTime 

     into varOneDay,varTwoDay,varThreeDay,varOneWeek,varTwoWeek,varOneMonth,varTwoMonth,
     varThreeMonth,varAtTheTime from licensetrackinterval;

     if varOneDay = 0 then
     update templicense set beforeOneDay= null ,afterOneDay= NULL;
     end if;

     IF varTwoDay = 0 THEN
     UPDATE templicense SET beforeTwoDay= NULL,afterTwoDay= NULL;
     END IF;

     IF varThreeDay = 0 THEN
     UPDATE templicense SET beforeThreeDay= NULL, afterThreeDay= NULL;
     END IF;

     IF varOneWeek = 0 THEN
     UPDATE templicense SET beforeOneWeek= NULL, afterOneWeek= NULL;
     END IF;

     IF varTwoWeek= 0 THEN
     UPDATE templicense SET beforeTwoWeek= NULL,afterTwoWeek= NULL;
     END IF;

     IF varOneMonth = 0 THEN
     UPDATE templicense SET beforeOneMonth= NULL,afterOneMonth= NULL;
     END IF;


     IF varTwoMonth = 0 THEN
     UPDATE templicense SET beforeTwoMonth= NULL,afterTwoMonth= NULL;
     END IF;

     IF varThreeMonth = 0 THEN
     UPDATE templicense SET beforeThreeMonth= NULL,afterThreeMonth= NULL;
     END IF;

     IF varAtTheTime = 0 THEN
     UPDATE templicense SET atTheTime= NULL;
     END IF;

     select * from templicense ;


     END$$

    DELIMITER ;



    sql mysql datetime database stored-procedure




    share|improve this question























      up vote
      3
      down vote

      favorite
      1









      up vote
      3
      down vote

      favorite
      1






      1





      I need to send email notifications for expired licenses:



      • One Day Before/After Expiration

      • Two Days Before/After Expiration

      • Three Days Before/After Expiration

      • One Week Before/After Expiration

      • Two Weeks Before/After Expiration

      • One Month Before/After Expiration

      • Two Months Before/After Expiration

      • Three Months Before/After Expiration

      • At the time of Expiration

      This is the Stored procedure to fetch expired licenses.



      DELIMITER $$

      DROP PROCEDURE IF EXISTS `licensetrack`$$

      CREATE PROCEDURE `licensetrack`()

      BEGIN

       DECLARE varOneDay INT;
       DECLARE varTwoDay INT;
       DECLARE varThreeDay INT;
       DECLARE varOneWeek INT;
       DECLARE varTwoWeek INT;
       DECLARE varOneMonth INT;
       DECLARE varTwoMonth INT;
       DECLARE varThreeMonth INT;
       DECLARE varAtTheTime INT;

       DROP TEMPORARY TABLE IF EXISTS templicense;

       CREATE TEMPORARY TABLE IF NOT EXISTS templicense(
       TYPE VARCHAR(50) NOT NULL,
       entityID BIGINT NOT NULL,
       expirationDate DATE,
       beforeOneDay DATE,
       afterOneDay DATE,
       beforeTwoDay DATE,
       afterTwoDay DATE,
       beforeThreeDay DATE,
       afterThreeDay DATE,
       beforeOneWeek DATE,
       afterOneWeek DATE,
       beforeTwoWeek DATE,
       afterTwoWeek DATE,
       beforeOneMonth DATE,
       afterOneMonth DATE,
       beforeTwoMonth DATE,
       afterTwoMonth DATE,
       beforeThreeMonth DATE,
       afterThreeMonth DATE,
       atTheTime DATE

       ) ;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'Organ' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM organizationlicenses orglic;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'Location' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM locationlicenses loclic;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'EmpLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM employeelicenses emplic;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'EmpDrivingLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM employeedriving drilic;

       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'EmpImgLicense' AS TYPE, ID AS entityID, expiredDate AS expirationDate,
       DATE_SUB(expiredDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expiredDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expiredDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expiredDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expiredDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expiredDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expiredDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expiredDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expiredDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expiredDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expiredDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expiredDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expiredDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expiredDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expiredDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expiredDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expiredDate, INTERVAL 0 DAY) atTheTime 
       FROM employeeimages empimglic;



       select oneDay,twoDay,threeDay,oneWeek,
       twoWeek,oneMonth , twoMonth,threeMonth ,atTheTime 

       into varOneDay,varTwoDay,varThreeDay,varOneWeek,varTwoWeek,varOneMonth,varTwoMonth,
       varThreeMonth,varAtTheTime from licensetrackinterval;

       if varOneDay = 0 then
       update templicense set beforeOneDay= null ,afterOneDay= NULL;
       end if;

       IF varTwoDay = 0 THEN
       UPDATE templicense SET beforeTwoDay= NULL,afterTwoDay= NULL;
       END IF;

       IF varThreeDay = 0 THEN
       UPDATE templicense SET beforeThreeDay= NULL, afterThreeDay= NULL;
       END IF;

       IF varOneWeek = 0 THEN
       UPDATE templicense SET beforeOneWeek= NULL, afterOneWeek= NULL;
       END IF;

       IF varTwoWeek= 0 THEN
       UPDATE templicense SET beforeTwoWeek= NULL,afterTwoWeek= NULL;
       END IF;

       IF varOneMonth = 0 THEN
       UPDATE templicense SET beforeOneMonth= NULL,afterOneMonth= NULL;
       END IF;


       IF varTwoMonth = 0 THEN
       UPDATE templicense SET beforeTwoMonth= NULL,afterTwoMonth= NULL;
       END IF;

       IF varThreeMonth = 0 THEN
       UPDATE templicense SET beforeThreeMonth= NULL,afterThreeMonth= NULL;
       END IF;

       IF varAtTheTime = 0 THEN
       UPDATE templicense SET atTheTime= NULL;
       END IF;

       select * from templicense ;


       END$$

      DELIMITER ;



      sql mysql datetime database stored-procedure




      share|improve this question













      I need to send email notifications for expired licenses:



      • One Day Before/After Expiration

      • Two Days Before/After Expiration

      • Three Days Before/After Expiration

      • One Week Before/After Expiration

      • Two Weeks Before/After Expiration

      • One Month Before/After Expiration

      • Two Months Before/After Expiration

      • Three Months Before/After Expiration

      • At the time of Expiration

      This is the Stored procedure to fetch expired licenses.



      DELIMITER $$

      DROP PROCEDURE IF EXISTS `licensetrack`$$

      CREATE PROCEDURE `licensetrack`()

      BEGIN

       DECLARE varOneDay INT;
       DECLARE varTwoDay INT;
       DECLARE varThreeDay INT;
       DECLARE varOneWeek INT;
       DECLARE varTwoWeek INT;
       DECLARE varOneMonth INT;
       DECLARE varTwoMonth INT;
       DECLARE varThreeMonth INT;
       DECLARE varAtTheTime INT;

       DROP TEMPORARY TABLE IF EXISTS templicense;

       CREATE TEMPORARY TABLE IF NOT EXISTS templicense(
       TYPE VARCHAR(50) NOT NULL,
       entityID BIGINT NOT NULL,
       expirationDate DATE,
       beforeOneDay DATE,
       afterOneDay DATE,
       beforeTwoDay DATE,
       afterTwoDay DATE,
       beforeThreeDay DATE,
       afterThreeDay DATE,
       beforeOneWeek DATE,
       afterOneWeek DATE,
       beforeTwoWeek DATE,
       afterTwoWeek DATE,
       beforeOneMonth DATE,
       afterOneMonth DATE,
       beforeTwoMonth DATE,
       afterTwoMonth DATE,
       beforeThreeMonth DATE,
       afterThreeMonth DATE,
       atTheTime DATE

       ) ;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'Organ' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM organizationlicenses orglic;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'Location' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM locationlicenses loclic;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'EmpLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM employeelicenses emplic;


       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'EmpDrivingLicense' AS TYPE, ID AS entityID, expirationDate AS expirationDate,
       DATE_SUB(expirationDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expirationDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expirationDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expirationDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expirationDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expirationDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expirationDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expirationDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expirationDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expirationDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expirationDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expirationDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expirationDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expirationDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expirationDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expirationDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expirationDate, INTERVAL 0 DAY) atTheTime 
       FROM employeedriving drilic;

       INSERT INTO templicense(TYPE,entityID,expirationDate,beforeOneDay,afterOneDay, beforeTwoDay,afterTwoDay,beforeThreeDay,afterThreeDay,beforeOneWeek,afterOneWeek,beforeTwoWeek,
       afterTwoWeek,beforeOneMonth,afterOneMonth,beforeTwoMonth,afterTwoMonth,beforeThreeMonth,afterThreeMonth,atTheTime)
       SELECT 'EmpImgLicense' AS TYPE, ID AS entityID, expiredDate AS expirationDate,
       DATE_SUB(expiredDate, INTERVAL 1 DAY) beforeOneDay, DATE_SUB(expiredDate, INTERVAL -1 DAY) afterOneDay,
       DATE_SUB(expiredDate, INTERVAL 2 DAY) beforeTwoDay, DATE_SUB(expiredDate, INTERVAL -2 DAY) afterTwoDay,
       DATE_SUB(expiredDate, INTERVAL 3 DAY) beforeThreeDay, DATE_SUB(expiredDate, INTERVAL -3 DAY) afterThreeDay,
       DATE_SUB(expiredDate, INTERVAL 1 WEEK) beforeOneWeek, DATE_SUB(expiredDate, INTERVAL -1 WEEK) afterOneWeek,
       DATE_SUB(expiredDate, INTERVAL 2 WEEK) beforeTwoWeek, DATE_SUB(expiredDate, INTERVAL -2 WEEK) afterTwoWeek,
       DATE_SUB(expiredDate, INTERVAL 1 MONTH) beforeOneMonth, DATE_SUB(expiredDate, INTERVAL -1 MONTH) afterOneMonth,
       DATE_SUB(expiredDate, INTERVAL 2 MONTH) beforeTwoMonth, DATE_SUB(expiredDate, INTERVAL -2 MONTH) afterTwoMonth,
       DATE_SUB(expiredDate, INTERVAL 3 MONTH) beforeThreeMonth, DATE_SUB(expiredDate, INTERVAL -3 MONTH) afterThreeMonth,
       DATE_SUB(expiredDate, INTERVAL 0 DAY) atTheTime 
       FROM employeeimages empimglic;



       select oneDay,twoDay,threeDay,oneWeek,
       twoWeek,oneMonth , twoMonth,threeMonth ,atTheTime 

       into varOneDay,varTwoDay,varThreeDay,varOneWeek,varTwoWeek,varOneMonth,varTwoMonth,
       varThreeMonth,varAtTheTime from licensetrackinterval;

       if varOneDay = 0 then
       update templicense set beforeOneDay= null ,afterOneDay= NULL;
       end if;

       IF varTwoDay = 0 THEN
       UPDATE templicense SET beforeTwoDay= NULL,afterTwoDay= NULL;
       END IF;

       IF varThreeDay = 0 THEN
       UPDATE templicense SET beforeThreeDay= NULL, afterThreeDay= NULL;
       END IF;

       IF varOneWeek = 0 THEN
       UPDATE templicense SET beforeOneWeek= NULL, afterOneWeek= NULL;
       END IF;

       IF varTwoWeek= 0 THEN
       UPDATE templicense SET beforeTwoWeek= NULL,afterTwoWeek= NULL;
       END IF;

       IF varOneMonth = 0 THEN
       UPDATE templicense SET beforeOneMonth= NULL,afterOneMonth= NULL;
       END IF;


       IF varTwoMonth = 0 THEN
       UPDATE templicense SET beforeTwoMonth= NULL,afterTwoMonth= NULL;
       END IF;

       IF varThreeMonth = 0 THEN
       UPDATE templicense SET beforeThreeMonth= NULL,afterThreeMonth= NULL;
       END IF;

       IF varAtTheTime = 0 THEN
       UPDATE templicense SET atTheTime= NULL;
       END IF;

       select * from templicense ;


       END$$

      DELIMITER ;




      sql mysql datetime database stored-procedure





      share|improve this question












      share|improve this question




      share|improve this question








      edited Jul 23 at 0:27









      Jamal♦

      30.1k11114225




      30.1k11114225









      asked Jul 18 at 4:06









      Sitansu S Swain

      1434




      1434

























          active

          oldest

          votes











          Your Answer




          StackExchange.ifUsing("editor", function ()
          return StackExchange.using("mathjaxEditing", function ()
          StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix)
          StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
          );
          );
          , "mathjax-editing");

          StackExchange.ifUsing("editor", function ()
          StackExchange.using("externalEditor", function ()
          StackExchange.using("snippets", function ()
          StackExchange.snippets.init();
          );
          );
          , "code-snippets");

          StackExchange.ready(function()
          var channelOptions =
          tags: "".split(" "),
          id: "196"
          ;
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function()
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled)
          StackExchange.using("snippets", function()
          createEditor();
          );

          else
          createEditor();

          );

          function createEditor()
          StackExchange.prepareEditor(
          heartbeatType: 'answer',
          convertImagesToLinks: false,
          noModals: false,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          );



          );








           

          draft saved


          draft discarded


















          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f199721%2fstored-procedure-for-sending-emails-at-specified-times-after-license-expiration%23new-answer', 'question_page');

          );

          Post as a guest






















          active

          oldest

          votes













          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes










           

          draft saved


          draft discarded


























           


          draft saved


          draft discarded














          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f199721%2fstored-procedure-for-sending-emails-at-specified-times-after-license-expiration%23new-answer', 'question_page');

          );

          Post as a guest
































































          -

          Popular posts from this blog

          Python Lists

          <!-- main_leaderboard, all: [728,90][970,90][320,50][468,60] --> Python Lists ❮ Previous Next ❯ Python Collections (Arrays) There are four collection data types in the Python programming language: List is a collection which is ordered and changeable. Allows duplicate members. Tuple is a collection which is ordered and unchangeable. Allows duplicate members. Set is a collection which is unordered and unindexed. No duplicate members. Dictionary is a collection which is unordered, changeable and indexed. No duplicate members. When choosing a collection type, it is useful to understand the properties of that type. Choosing the right type for a particular data set could mean retention of meaning, and, it could mean an increase in efficiency or security. List A list is a collection which is ordered and changeable. In Python lists are written with square brackets. Example Create a List: thislist = ["apple", "banana", ...
          Read more

          Aion

          Image
          Aion - 6+ , 5417 Aion - . MMORPG, World of Warcraft, World of Tanks My Lineage II . CryEngine. , . : Aion https://ru.4game.com/aion/ Aion , . , , . , , . , , , . . , , , . . : . , . , , . , , . 12 , 2 : ( ). ( ). ( ). ( ) ( ). ( ). , . , : . . , . , , . . , , . Aion, , . , . , . : . . . . , : , , , , , . , , , . : Aion https://ru.4game.com/aion/install/ : . , . , . , Aion . , . . , . , . , , . . : . . . , ( !), MMORPG Aion . , . ...
          Read more

          JavaScript Array Iteration Methods

          <!-- main_leaderboard, all: [728,90][970,90][320,50][468,60] --> JavaScript Array Iteration Methods ❮ Previous Next ❯ Array iteration methods operate on every array item. Array.forEach() The forEach() method Calls a function once for each array element. Example var txt = ""; var numbers = [4, 9, 16, 25]; numbers.forEach(myFunction); function myFunction(value, index, array) {     txt = txt + item + "<br>"; } Try it Yourself » Note that the function takes 3 arguments: The item value The item index The array itself Array.forEach() is not supported in Internet Explorer 8 or earlier: Method forEach() Yes 9.0 Yes Yes Yes Array.map() The map() method creates a new array by performing a function on each array element. This example multiplies each array value by 2: Example var numbers1 = [4, 9, 16, 25]; var numbers2 = numbers1.map(myFunction); function m...
          Read more