Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Summary Portion of Query returns wrong values
Message
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Summary Portion of Query returns wrong values
Miscellaneous
Thread ID:
00717292
Message ID:
00717292
Views:
37
I'm still working on this darn thing, thought I had it until I went through the details. If I just run the detail part of the query by itself, I get the right number of detail records 33. If I run the complete thing returning the summary values, I get a doc count of 27. I've got it filtered for testing to just one doctor.

Thanks for any help.
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
Next
Reply
Map
View

Click here to load this message in the networking platform