I am running the following Command query against SQL-Server 2005
SELECT DISTINCT
"PatientProfile"."Last",
"PatientProfile"."First",
"PatientProfile"."PatientId",
...
"PROBLEM"."CODE",
"PROBLEM"."DESCRIPTION",
"PROBLEM"."ONSETDATE",
"PROBLEM"."CHANGE" ,
"PROBLEM"."STOPDATE",
"PROBLEM"."QUALIFIER",
"LASTLABS1"."HDID" "HDID1",
"RPTOBS1"."obsdate" "obsdate1",
"RPTOBS1"."change" "change1",
"RPTOBS1"."obsvalue" "obsvalue1",
...
"LASTLABS9"."HDID" "HDID9",
"RPTOBS9"."obsdate" "obsdate9",
"RPTOBS9"."change" "change9",
"RPTOBS9"."obsvalue" "obsvalue9"
FROM
(("CentricityPS"."dbo"."PatientProfile" "PatientProfile" INNER JOIN
"CentricityPS"."dbo"."PROBLEM" "PROBLEM" ON
"PatientProfile"."PId"="PROBLEM"."PID") LEFT OUTER JOIN
"CentricityPS"."dbo"."LASTLABS" "LASTLABS1" ON
("PatientProfile"."PId"="LASTLABS1"."PID" AND
"LASTLABS1"."HDID"=28) LEFT OUTER JOIN
"CentricityPS"."dbo"."RPTOBS" "RPTOBS1" ON
("LASTLABS1"."HDID"="RPTOBS1"."hdid" AND
"LASTLABS1"."MAXLABDATE"="RPTOBS1"."obsdate" AND
"LASTLABS1"."PID"="RPTOBS1"."pid" AND
"RPTOBS1"."change"=2) LEFT OUTER JOIN
...
"CentricityPS"."dbo"."LASTLABS" "LASTLABS9" ON
("PatientProfile"."PId"="LASTLABS9"."PID" AND
"LASTLABS9"."HDID"=18892) LEFT OUTER JOIN
"CentricityPS"."dbo"."RPTOBS" "RPTOBS9" ON
("LASTLABS9"."HDID"="RPTOBS9"."hdid" AND
"LASTLABS9"."MAXLABDATE"="RPTOBS9"."obsdate" AND
"LASTLABS9"."PID"="RPTOBS9"."pid" AND
"RPTOBS9"."change"=2))
WHERE
"PatientProfile"."ispatient"='Y' AND
"PatientProfile"."pstatus"='A' AND
"PatientProfile"."Birthdate"<{ts '1992-03-04 00:00:01'} AND
("PROBLEM"."CODE" LIKE 'ICD-249%' OR
"PROBLEM"."CODE" LIKE 'ICD-250%' OR
"PROBLEM"."CODE"='ICD-V77.1') AND
"PROBLEM"."CHANGE"=2 AND
"PROBLEM"."QUALIFIER"<>'FH OF' AND
("PROBLEM"."STOPDATE" IS NULL OR
"PROBLEM"."STOPDATE">={ts '2010-03-05 00:00:00'})
There are a total of 9 Left Outer Joins to give a total of 9 Observations and Obsdates in the result table besides the demograhics. Running the Query in the SQL-Server Query window gives the expected result.
However, running a Crystal Report in either 11.5 or 12 with a Summary Cross-Tab Tables and Charts works for the first 6 Obs, then gives randm results in the last 3.