SSL certificate Expiry Notification report

[grishma_kadam]

We have an analytics query to notify users if SSL certificate is due for renewal

Below is the one that is already set
select DISTINCT ss.alias, ss.valid_to, '' as connectionname from users u, sav_sslcert ss, externalconnection ec where userkey in (select userkey from user_savroles where rolekey in (select rolekey from savroles where rolename='ROLE_ADMIN')) AND cert_key NOT IN ( select cert_key from sav_sslcert ss, externalconnection ec where ss.cert_key=ec.sslcertificate ) AND DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) < (select configdata from configuration where name='DAYS_BEFORE_CERTIFICATE_EXPIRY') AND DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) > 0

And this is what we have come up with

SELECT distinct E.CONNECTIONNAME,
       S.ALIAS,
       S.VALID_FROM,
       S.VALID_TO AS certificate_expiry
FROM   EXTERNALCONNECTION E,
       SAV_SSLCERT S WHERE  S.CERT_KEY = E.SSLCERTIFICATE
       AND E.SSLCERTIFICATE IS NOT NULL AND DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) < (select configdata from configuration where name='DAYS_BEFORE_CERTIFICATE_EXPIRY') AND DATEDIFF(VALID_TO, CURRENT_TIMESTAMP()) > 0

Could you please shed some light on which is correct and why other is incorrect ?

[rushikeshvartak]

SELECT DISTINCT E.CONNECTIONNAME,
                S.ALIAS,
                S.VALID_FROM,
                S.VALID_TO AS certificate_expiry
FROM   EXTERNALCONNECTION E, 
       SAV_SSLCERT S 
WHERE  S.CERT_KEY = E.SSLCERTIFICATE 
       AND E.SSLCERTIFICATE IS NOT NULL 
       AND Datediff(VALID_TO, CURRENT_TIMESTAMP()) < (SELECT configdata 
                                                      FROM   configuration 
                                                      WHERE  name = 'DAYS_BEFORE_CERTIFICATE_EXPIRY') 
       AND Datediff(VALID_TO, CURRENT_TIMESTAMP()) > 0