Minimum Function 1

[dixie617]

I have a report where I only one the first record for each patient and then the report is grouped by provider, patient but I am still getting both records instead of one, this is what I am using in the Group Selection:

{DOCUMENT.CLINICALDATE} = Maximum ({DOCUMENT.CLINICALDATE}, {PERSON.PATIENTID}) and
{AUDIT_EVENT.EVENT_ID} = minimum({AUDIT_EVENT.EVENT_ID}, {PERSON.PATIENTID})

I am only selecting a 30 day period of visits, I am asking for two events what they call an initial and a final, if there in an initial and a final I want the initial, if there is only a final I want the final. I figure the best was if to select the first one for each right? But I am getting both no matter what I have tried. Today I just think Crystal hates me.

Thanks Dixie

 

[lbass]

You should be able to use the same method, yes.

-LB

 

[dixie617]

LB, just figured out it worked but not, I am only getting about 30 records now where their should be a lot more

 

[dixie617]

I forgot to add the SQL Query from the report, is it suppose to have more than one select statement now:


SELECT "PERSON"."PATIENTID", "RPTOBS"."HDID", "RPTOBS"."OBSDATE",
"PERSON"."PSTATUS", "RPTOBS"."OBSVALUE", "PERSON"."EXTERNALID",
"PERSON"."DATEOFBIRTH", "PERSON"."PID", "PERSON"."HOMELOCATION"

FROM "ML"."RPTOBS" "RPTOBS" INNER JOIN "ML"."PERSON" "PERSON" ON
"RPTOBS"."PID"="PERSON"."PID"

WHERE "PERSON"."PSTATUS"='A'

SELECT "PERSON"."PATIENTID", min("AUDIT_EVENT"."EVENT_ID") "MinEvID"FROM
(("ML"."DIRECTIV" "DIRECTIV" INNER JOIN (((("ML"."AUDIT_EVENT"
"AUDIT_EVENT" INNER JOIN ("ML"."RPTOBS" "RPTOBS" INNER JOIN "ML"."PERSON"
"PERSON" ON "RPTOBS"."PID"="PERSON"."PID") ON
"AUDIT_EVENT"."PID"="PERSON"."PID") INNER JOIN "ML"."DOCUMENT" "DOCUMENT"
ON ("AUDIT_EVENT"."SDID"="DOCUMENT"."SDID") AND
("PERSON"."PID"="DOCUMENT"."PID")) INNER JOIN "ML"."AUDIT_PROFILE"
"AUDIT_PROFILE" ON
"AUDIT_EVENT"."PROFILE_ID"="AUDIT_PROFILE"."PROFILE_ID") INNER JOIN
"ML"."USRINFO" "USRINFO" ON "AUDIT_PROFILE"."PVID"="USRINFO"."PVID") ON
"DIRECTIV"."PID"="PERSON"."PID") INNER JOIN "ML"."PROBLEM" "PROBLEM" ON
"PERSON"."PID"="PROBLEM"."PID") INNER JOIN "ML"."LOCREG" "LOCREG" ON
"PERSON"."HOMELOCATION"="LOCREG"."LOCID"WHERE "PERSON"."PSTATUS"='A' AND
"DOCUMENT"."DOCTYPE"=1 AND "DOCUMENT"."SUMMARY" LIKE '%Diabetes%' AND NOT
("DOCUMENT"."SUMMARY" LIKE 'Appointment No Show%' OR "DOCUMENT"."SUMMARY"
LIKE 'DNKA%' OR "DOCUMENT"."SUMMARY" LIKE 'Print Prenatal%') AND
("LOCREG"."ABBREVNAME" LIKE 'LW%' OR "LOCREG"."ABBREVNAME" LIKE 'MA%' OR
"LOCREG"."ABBREVNAME" LIKE 'N MA%') AND "PROBLEM"."CODE" LIKE 'ICD-250%'
AND ("AUDIT_EVENT"."EVENT_TYPE_ID"=120 OR
"AUDIT_EVENT"."EVENT_TYPE_ID"=144) and"DOCUMENT"."DB_CREATE_DATE" > {fn
curdate()}-31 and"DOCUMENT"."DB_CREATE_DATE" <= {fn curdate()}GROUP By
"PERSON"."PATIENTID"



SELECT "PROBLEM"."CODE", "LOCREG"."ABBREVNAME", "USRINFO"."LASTNAME",
"USRINFO"."FIRSTNAME", "DOCUMENT"."CLINICALDATE", "DOCUMENT"."DOCTYPE",
"DOCUMENT"."SUMMARY", "DIRECTIV"."DESCRIPTION",
"AUDIT_EVENT"."EVENT_TYPE_ID", "AUDIT_EVENT"."EVENT_ID", "DOCUMENT"."PID",
"DOCUMENT"."SDID", "PROBLEM"."PID", "LOCREG"."LOCID", "DIRECTIV"."PID",
"AUDIT_EVENT"."SDID", "AUDIT_EVENT"."PID"

FROM (("ML"."DIRECTIV" "DIRECTIV" CROSS JOIN ((("ML"."AUDIT_EVENT"
"AUDIT_EVENT" INNER JOIN "ML"."DOCUMENT" "DOCUMENT" ON
"AUDIT_EVENT"."SDID"="DOCUMENT"."SDID") INNER JOIN "ML"."AUDIT_PROFILE"
"AUDIT_PROFILE" ON
"AUDIT_EVENT"."PROFILE_ID"="AUDIT_PROFILE"."PROFILE_ID") INNER JOIN
"ML"."USRINFO" "USRINFO" ON "AUDIT_PROFILE"."PVID"="USRINFO"."PVID"))
CROSS JOIN "ML"."PROBLEM" "PROBLEM") CROSS JOIN "ML"."LOCREG" "LOCREG"

WHERE ("DOCUMENT"."SUMMARY" LIKE 'Adult%' OR "DOCUMENT"."SUMMARY" LIKE
'AM Adult Medicine%') AND "DOCUMENT"."DOCTYPE"=1 AND
("LOCREG"."ABBREVNAME" LIKE 'LW%' OR "LOCREG"."ABBREVNAME" LIKE 'MA%' OR
"LOCREG"."ABBREVNAME" LIKE 'N MA%') AND "PROBLEM"."CODE" LIKE 'ICD-250%'
AND ("AUDIT_EVENT"."EVENT_TYPE_ID"=120 OR
"AUDIT_EVENT"."EVENT_TYPE_ID"=144)

 

[lbass]

If you add a command to an existing report, the linking will occur locally, so the report will be slower and the query will show the command and the regular query as if they are two separate queries--because the linking is occurring locally. Ideally you would create one command as your datasource, where the subselect is one "field" within the select clause. I didn't suggest that because you have many existing reports, and they would have to be redone to use the one command method.

I'm not sure why you are seeing three queries though. You are also showing crossjoins which I think you want to avoid. I think you should go into the database expert and check all of your linking. Make sure that you don't have two links from two different tables leading to the same table. Two table should not point to the same table.

-LB

 

[dixie617]

I think I understand, I did have two links from one table going to the same table. I am getting a warning:

"More than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed.

 

[lbass]

Just ignore this--the report should run okay.

-LB