I am trying to get a count on how many cases have been scheduled for a particular doctor as part of the query below, but if there is more than one child record with the doctor on it, it counts both. Here is the basic layout with 1 record for the meeting itself and two procedures attached to it for the same doc.

Meetings Table
---------------------------------------------------------------
meetingnumber   begintime            endtime             otherfields............
1               10/24/2002 07:00     10/24/2002 10:00

MeetProc Table
meetprocid  meetingnumber   coPerID    ProcID
-------------------------------------------------------
1            1               189        7141
2            1               189        1021

Here is the code from my stored procedure

SELECT dbo.cospec.specdesc, 
	RTRIM(dbo.coper.lastname) + ', ' + RTRIM(dbo.coper.firstname) AS Surgeon, 
	count(dbo.coPer.coPerid) as DocCount,
	sum(datediff(mi,meetings.enteror,meetings.exitor)) as ORMinutes,	
	sum(case
	   when dbo.ctAdmissionType.inOrOut=1 then 1
	   else 0
	end) as InCnt,
	sum(case
	   when dbo.ctAdmissionType.inOrOut=1 then datediff(mi,enteror,exitor)
	   else 0
	end) as InMinutes,
	sum(case
	   when dbo.ctAdmissionType.inOrOut=0 then datediff(mi,enteror,exitor)
	   else 0
	end) as OutMinutes,
	sum(case
	   when dbo.ctAdmissionType.inOrOut=0 then 1
	   else 0
	end) as OutCnt,
	sum(datediff(mi,meetings.AnesStart,meetings.AnesEnd)) as AnesMinutes,	
	sum(datediff(mi,meetings.PacuStart,meetings.PacuEnd)) as PACUMinutes,	
	coPer.SpecID, coPer.coPerID

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
group by cospec.specdesc,dbo.coper.specid,dbo.coPer.coPerID,dbo.coper.lastname,dbo.coper.firstname
ORDER BY cospec.specdesc,dbo.coper.lastname, dbo.coper.firstname

Thanks for any help
Kirk