Show patients not seen by current pcp

[records333]

I'm learning and I would like **HELP** on a formula that capture Patient panel for specific prov id and show patients not seen by current pcp. I have the following formula but it is not returning those NOT seen.
{CLARITY_SER.PROV_ID} = "11111" and
{PATIENT.PAT_STATUS_C} = "?" and
{CLARITY_SER_vstpcp.PROV_ID} <> "11111

 

[IanWaterman]

This will be an issue relating to joins in your data.

you probably need a left outer join to a table which only holds data relating to patients seen. Using a l/o join will bring back all patients seen or not seen.

If you use a l/o join you can then put a condition on the joined table as the filter condition will over ride the l/o join.

Ian

 

[CoSpringsGuy]

once you implement Ians solution left join suggestion, use something like this in your record selection formula


isnull({CLARITY_SER_vstpcp.PROV_ID})

this will return all patients that do not have a pcp match in the "right" table

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[records333]

Let me try and re-phrase better. I would like to identify patients assigned to pcp AND never had a visit with assigned PCP. I'm using clarity database.

 

[records333]

Hmm...not working. Let me rephrase and include information. I am using crystal reports and I would like to show records of patients assigned to PCP and not seen by assigned PCP.

 

[pmax9999]

Can you provide a bit more detail about the data structure.

I am guessing that the table PATIENT contains the patient details, CLARITY_SER contains details of providers/visits and CLARITY_SER_vstpcp is an alias for the CLARITY_SER table. My questions are:
> Am I right so far?
> Does the CLARITY_SER table contain all visits or only the most recent visit?
> What identifies which provider the patient is allocated to (table and field);
> What is the relationship between the tables involved (ie, how are they joined)?
> Can a patient be assigned to more than one provider? Can the allocated provider change, and if so how is that dealt with in the data?
> Is it possible that a patient does not have an allocated provider?

At this point any suggestion made will be a guess based on assumptions. The more information you can provide about the data structure, the more likely someone will be able to provide assistance.

Pete.

 

[records333]

Hi there! Yes you are correct in regards to what each table contains.
A:Clarity_Ser details about all physicians
Aatient.cur_pcp_prov_id identifies which patients are assigned to physicians
A: A patient cannot be assigned to more than 1 provider
A: Yes, an allocated provider can change PCP (identified in a few different ways)
A: Yes it is possible that a PCP does not have an allocated provider

Joins:

pat_enc.pat_id>patient.pat_id (inner join)
Patient.PatID>Pat_Enc_Appt.pat_id (inner join)
pat_enc_appt.prov_id>Clarity_ser.provid (inner join)

 

[pmax9999]

OK, still trying to join the dots here but I am just confused (not unusual for me I admit - its an age thing I suspect), and there seems to be a couple more tables not previously mentioned (pat_enc, Pat_Enc_Appt).

Can you provide please a list of relevant tables, a brief description of the record contained, plus a list of relevant columns (fields) from each of those tables (as relevant to the report) and a description if not obvious from the name (eg "pat_id" and "provid" seem self explanatory, but others may not mean anything to someone from outside your industry such as me).

I am sure me or someone will be able to help you, but you need to give us all of the information.

Cheers
Pete

 

[aeljhmi]

What if you link your tables as follows:
left outer join from PATIENT.PAT_ID to PAT_ENC.PAT_ID
left outer join from PATIENT.CUR_PCP_PROV_ID to CLARITY_SER_vstpcp.PROV_ID
left outer join from PAT_ENC.VISIT_PROV_ID to CLARITY_SER.PROV_ID

Group your report by PATIENT.PAT_ID - you can then add PATIENT.PAT_NAME to GH1 as well as CLARITY_SER_vstpcp.PROV_NAME
Add PAT_ENC.VISIT_PROV_ID and PAT_ENC.PCP_PROV_ID to the details section. (My thinking is that if these two fields match for any encounter, then the patient has seen their PCP.) Limit your report results using record select:
not isnull ({PATIENT.CUR_PCP_PROV_ID}) and not isnull({PAT_ENC.VISIT_PROV_ID})
This should limit your results to only those patients who have a PCP assigned and only those encounters where a provider was seen.
You will also want to conditionally suppress your details section as follows: {PAT_ENC.PCP_PROV_ID} = {PAT_ENC.VISIT_PROV_ID}
This will display only those encounters where a provider was seen that was not the PCP. (you can also do the converse '<>' to show the encounters where the PCP was seen).
You can use the two CLARITY_SER instances to fill in names etc.

 

[records333]

I will try your solution- thanks!

In the interim, I created a SQL command :

select pat_id from patient where cur_pcp_prov_id='?11111' and pat_status_c='1'
minus
select distinct pat_id from pat_enc where VISIT_PROV_ID='11111' and
pat_id in (select pat_id from patient where cur_pcp_prov_id='11111' and pat_status_c='1')

I added above to .rpt and used SQL command.pat_id and I GOT WHAT I NEEDED!!Using SQL proved to be less complicated than Crystal- in this case. BUT I WILL BE TRYING YOURS AS WELL.

THANKS TO ALL THAT ASSISTED ME!!!