select datepart( hour, opr.fsl_choIdDate ) as byHour, format( fsl_choIdDate, 'hh tt' ) as byHourFullText, 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) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId inner join tbl_FSPRO_Members mem with (nolock) on mem.fspro_userId = opr.fsl_choIdBy where nullif( opr.fsl_choIdDate, '' ) is not null and opr.fsl_choId > 0 and convert( datetime, convert( varchar, opr.fsl_choIdDate, 101 )) >= and convert( datetime, convert( varchar, opr.fsl_choIdDate, 101 )) < and mem.email = and mem.email in ( ) and opr.ownerId = and opr.fsltablecode = group by datepart( hour, opr.fsl_choIdDate ), format( fsl_choIdDate, 'hh tt' ), convert( varchar, opr.fsl_choIdDate, 101 ) order by datepart( hour, opr.fsl_choIdDate ) for json auto, include_null_values