select det.specDesc, det.surgeon, count(det.doccount) as doccount, sum(det.ORMinutes) as orMinutes, sum(det.AnesMinutes) as AnesMinutes, sum(det.PACUMinutes) as pacuMinutes, sum(det.InCnt) as InCnt, sum(det.OutCnt) as OutCnt, sum(det.InMinutes) as inMinutes, sum(det.OutMinutes) as OutMinutes, det.specid, det.coPerID From (SELECT distinct TOP 100 percent dbo.cospec.specdesc, RTRIM(dbo.coper.lastname) + ', ' + RTRIM(dbo.coper.firstname) AS Surgeon, dbo.coPer.coPerID, dbo.meetings.SpecID, dbo.DocCount(1) as doccount, isnull(datediff(mi,EnterOR,ExitOR),0) as ORMinutes, isnull(datediff(mi,anesstart,isnull(anesend,anesstart)),0) as AnesMinutes, isnull(datediff(mi,pacustart,isnull(pacuend,pacustart)),0) as PACUMinutes, case when isnull(dbo.ctAdmissionType.inOrOut,0)=1 then 1 else 0 end as InCnt, case when isnull(dbo.ctAdmissionType.inOrOut,0)=1 then datediff(mi,enteror,exitor) else 0 end as InMinutes, case when isnull(dbo.ctAdmissionType.inOrOut,0)=0 then datediff(mi,enteror,exitor) else 0 end as OutMinutes, case when isnull(dbo.ctAdmissionType.inOrOut,0)=0 then 1 else 0 end as OutCnt FROM dbo.meetings INNER JOIN dbo.meetproc ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN dbo.coper ON dbo.meetproc.coperid = dbo.coper.coperid INNER JOIN dbo.ctAdmissionType ON dbo.meetings.AdmitTypeID = dbo.ctAdmissionType.AdmitTypeID INNER JOIN dbo.coproc ON dbo.meetproc.procid = dbo.coproc.procid AND dbo.meetproc.procid = dbo.coproc.procid INNER JOIN dbo.patients ON dbo.meetings.patientid = dbo.patients.patientid INNER JOIN dbo.corooms ON dbo.meetings.roomid = dbo.corooms.roomid INNER JOIN dbo.coCaseTypes ON dbo.meetings.CaseTypeID = dbo.coCaseTypes.CaseTypeID INNER JOIN dbo.cospec ON dbo.coper.specid = dbo.cospec.specid WHERE (dbo.meetings.iscompleted = 1 ) and dbo.meetings.begintime between @ttStartDate and @ttEndDate AND dbo.meetings.deptid=@tiDeptID and dbo.meetings.iscancelled=0 and dbo.meetings.isblock=0 and dbo.meetproc.coperid=236 ORDER BY cospec.specdesc, det.surgeon) det group by det.specdesc, det.surgeon,det.coPerID,det.SpecID