Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Summary Portion of Query returns wrong values
Message
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Titre:
Summary Portion of Query returns wrong values
Divers
Thread ID:
00717292
Message ID:
00717292
Vues:
38
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
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform