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]

Try:

{DOCUMENT.CLINICALDATE} = Minimum ({DOCUMENT.CLINICALDATE}, {PERSON.PATIENTID})

If there is only one eventID for the patient, the minimum will pick that up even if it is the "final", and if there are two, the minimum will pick up the "initial".

-LB

 

[dixie617]

Thanks lb, Still getting both entries is because of the groupings? I have two groups within this report, Patient ID and Provider Name. With the patients with two entries for the same document I have two providers the inital signature is a mid-level and the final is the supervising provider. The initial is showing up where it should under the mid-level, but the final for the same document is also showing up under the supervising provider (this I don't want) Not sure where to go from here

Dix

 

[lbass]

Which group is group #1?

-LB

 

[dixie617]

The provider is Group #1, PatientID is Group #2

 

[lbass]

Are you saying you only want one reference to a patient regardless of provider? Please identify your CR version.

-LB

 

[lbass]

I also need to know how your tables are currently linked.

-LB

 

[dixie617]

Yes, that is exactly what I need, if it is an Initial and a final I want the initial on the provider that did it and I don't want the final for the supervising provider at all. If is just a final than that is all I won't also. CRXI is what I am using.

Thanks Dix

 

[dixie617]

There are nine tables total to pull all this info together, this is a most tables are linked to the patient record and the patient record is linked to the provider, the provider is linked to the Audit event where the initial and final are located. Here is the SQL Query from the report:

SELECT "PROBLEM"."CODE", "PERSON"."PATIENTID", "LOCREG"."ABBREVNAME",
"USRINFO"."LASTNAME", "USRINFO"."FIRSTNAME", "RPTOBS"."HDID",
"DOCUMENT"."CLINICALDATE", "RPTOBS"."OBSDATE", "DOCUMENT"."DOCTYPE",
"PERSON"."PSTATUS", "RPTOBS"."OBSVALUE", "DOCUMENT"."SUMMARY",
"DIRECTIV"."DESCRIPTION", "PERSON"."EXTERNALID", "PERSON"."DATEOFBIRTH",
"AUDIT_EVENT"."EVENT_TYPE_ID", "AUDIT_EVENT"."EVENT_ID"

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)

 

[dixie617]

one more thing, if we can figure this out I will owe you big time. I have to apply this to about 20 other reports. Thanks Dix

 

[lbass]

Which table is the provider table? And Person = Patient?
Is it possible to link person to audit event directly? What fields would you then link on? Also, how are you limiting records to 30 days?

My thought was that you should create a SQL expression that captured the minimum event ID per person within that period. You would then select data by setting the eventID equal to the SQL expression--then there would be only one event per patient.

-LB

 

[dixie617]

UserInfo is the provider table, person is the patient. The Audit Event is linked to three tables, Person.PID, Audit Profile.PVID (which identifies the user) and the Document.SPID table. AS far as the 30 days go, I schedule these reports to run on the 16th of each month for the period from 16th of previous to 15th of current month. This is what I use, probably not exact {@ClinicalDAte} in [(Currentdate) _to_ (currentdate - 32)]

I have never done a sql expression but I can learn

Dix

 

[lbass]

Again:

Is it possible to link person to audit event directly? What fields would you then link on?

And what is the content of your formula {@ClinicalDate}?

-LB

 

[dixie617]

Audit_Event.PID is linked directly to Person.PID, and then it is also linked to the Audit_Profile.Profile_ID because that is the only way to find out who signed the document and it is also linked to the Document.SDID. The ClinicalDate is a datetime in seconds, the formula just converts to an actual datetime. I could send you a snapshot of the table links but can't figure out how to attach a document to this.

Dix

 

[lbass]

Okay, the SQL expresssion route is too complex so try this instead. Go to database->show SQL query and copy the query. Then go to database->database expert->add command and paste in the query. You need to add the date restriction to the where clause for this to work--in fact all the selection criteria would need to appear in the SQL. Remove all fields in the Select clause except for event_ID and Patient ID and then wrap the event ID in min(). Add a group by clause at the end as follows:

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"."CLINICALDATE" > {fn curdate()}-31 and
"DOCUMENT"."CLINICALDATE" <= {fn curdate()}
GROUP By "PERSON"."PATIENTID"

Next link the command to the Person table on Patient ID. Then go into report->selection formula and add the following:

{AUDIT_EVENT.EVENT_ID} = {Command.MinEvID}

-LB

 

[dixie617]

I got the following error:
Failed to retrieve data from the database.
Details: HY000:[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: epected NUMBER got DATE.

Is maybe because of clinical date

Thanks Dix

 

[dixie617]

I changed the date field to the create date and it worked, one more question, I need to take out the record selection that I already had correct because it is in the SQL Cmd?

 

[lbass]

I think it is better to leave the current record selection as is, and just add:

and
{AUDIT_EVENT.EVENT_ID} = {Command.MinEvID}

...to it, because this last will occur locally and slow your report, so you should be starting with the minimum number of records that you can in the main report.

It was a good question though, as I should have mentioned that if you otherwise change the criteria in your main report selection formula, you will need to change them in the command as well.

-LB

 

[dixie617]

Thanks LB for all of the help,

So if this works (which I will know shortly is currently running) I can then apply the same, matching the records selection to the other reports this applies to?

Dix

 

[dixie617]

It worked perfect thank you so much