I need to eliminate the rows, if the enrollDate and cancelDate is same. So i wrote the query as follows SELECT a.id, a.clientId, a.enrollDate, a.cancelDate FROM gap_borrower a WHERE STR_TO_DATE( a.loanEndDate, '%Y-%m-%d' ) >= STR_TO_DATE( '2015-12-31', '%Y-%m-%d' ) AND FIND_IN_SET( a.status, 'A,C' ) AND STR_TO_DATE( a.enrollDate, '%Y-%m-%d' ) <> STR_TO_DATE( a.cancelDate, '%Y-%m-%d' )
But if cancelDate is Null, it didn't display as different.(Only need to eliminate the id=8551 in the above figure. This is my problem) So i wrote another query using if condition
SELECT a.id, a.clientId, a.enrollDate, a.cancelDate FROM gap_borrower a WHERE STR_TO_DATE( a.`loanEndDate` , '%Y-%m-%d' ) >= STR_TO_DATE( '2015-12-31', '%Y-%m-%d' ) AND FIND_IN_SET( a.status, 'A,C' ) AND (CASE WHEN a.cancelDate IS NOT NULL THEN (STR_TO_DATE( a.`enrollDate` , '%Y-%m-%d' ) <> STR_TO_DATE( a.cancelDAte, '%Y-%m-%d' )) END )
Its also fails.Please guide me to fix this
Aucun commentaire:
Enregistrer un commentaire