select m.firstName, m.lastName, m.email, count( cdr.cdr_recordId ) as totalRecords, convert( varchar, cdr.fsl_dstIdDate, 101 ) as mappedDate, sum( case when dateDiff( n, dst.dateCreated, cdr.fsl_dstIdDate ) <= 1 then 1 else 0 end ) newRecord, sum( case when dateDiff( n, dst.dateCreated, cdr.fsl_dstIdDate ) > 1 or dst.dateCreated is null then 1 else 0 end ) existingRecord from tbl_CDR_Distributors cdr with (nolock) left join tbl_DST_Distributors dst with (nolock) on dst.dstid = cdr.fsl_dstId left join tbl_FSPRO_Members m with (nolock) on m.fspro_userId = cdr.fsl_dstIdBy where nullif( cdr.fsl_dstIdDate, '' ) is not null and nullif( cdr.fsl_dstId, 0 ) is not null and m.email in ( ) and convert( datetime, convert( varchar, cdr.fsl_dstIdDate, 101 )) >= and convert( datetime, convert( varchar, cdr.fsl_dstIdDate, 101 )) < and cdr.ownerId = and cdr.fsl_tablecode = group by m.firstName, m.lastName, m.email, convert( varchar, cdr.fsl_dstIdDate, 101 ) order by m.lastName