select m.firstName, m.lastName, m.email, count( opr.operatorId ) as totalRecords, convert( varchar, opr.fsl_choIdDate, 101 ) as mappedDate, sum( case when dateDiff( n, cho.dateCreated, opr.fsl_choIdDate ) <= 1 then 1 else 0 end ) newRecord, sum( case when dateDiff( n, cho.dateCreated, opr.fsl_choIdDate ) > 1 or cho.dateCreated is null then 1 else 0 end ) existingRecord from tbl_OPR_ClientOperators opr with (nolock) left join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left join tbl_FSPRO_Members m with (nolock) on m.fspro_userId = opr.fsl_choIdBy where nullif( opr.fsl_choIdDate, '' ) is not null and nullif( opr.fsl_choId, 0 ) is not null and m.email in ( ) and convert( datetime, convert( varchar, opr.fsl_choIdDate, 101 )) >= and convert( datetime, convert( varchar, opr.fsl_choIdDate, 101 )) < and opr.ownerId = and opr.fsltablecode = group by m.firstName, m.lastName, m.email, convert( varchar, opr.fsl_choIdDate, 101 ) order by m.lastName