Sergey

Never mind, I think I got it




I am trying to implement your idea, but I'm missing something, I get an error saying that:

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 156: Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near the keyword 'ORDER'.
---------------------------
OK
---------------------------

select 	det.meetingnumber,
	det.specdesc,
     	det.surgeon,
	det.doccount, 
	det.orMinutes, 
	det.inCnt,
	det.InMinutes,
	det.OutMinutes,
	det.OutCnt,
	det.ANesMinutes,
	det.PacuMinutes,
	det.specID, 
	det.coPerID,

FROM (SELECT  Top 100 percent dbo.meetings.meetingnumber,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
	ORDER BY cospec.specdesc,dbo.coper.lastname, dbo.coper.firstname )   Det
group by det.meetingnumber

Thanks