select crsVenue, crsBriefDesc, crsDesc, crdDateTime, crdCode , crsGroupCode, crsCourseOrSes, crsCounterID, cntcounterid, isnull(cntCount,888) cntCount,
"NoOfEn" =
(
select sum(cenHeads) from Enrolments
where cenEnrolDate = cd.crdDateTime
and cenCode = crdCode
and cenGroupCode = crdGroupCode
and cenCourseOrSes = crsCourseOrSes
and cenVenue = crsVenue
and cast(cenEnrolDate as DATE) = cast(crdDateTime as DATE)
)
Into #Tmp
from Courses cs
inner join coursedates cd
on cd.crdgroupcode = cs.crsGroupCode and cd.crdCode = cs.crsCode and cd.crdCourseorSes = cs.crsCourseOrSes and cd.crdvenue = cs.crsvenue
left join Counter cn
on cs.crsCounterID = cn.cntCounterID and cs.crsVenue = cn.cntVenue and DATEADD(dd, 0, DATEDIFF(dd, 0, cd.crdDateTime)) = cn.cntDate
where crdDateTime > '01 jan 2013'
and crsCounterID > 0

select * from #Tmp
where cntCount <> NoOfEn
order by crdDateTime

drop table #Tmp