• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

How to filter out patients not seen at other locations

I have a list of patients who were seen in 2014 at one location.  I want to query the database and look at all other locations and their patients.  If the patients on my list from the one location have NOT been at any other location, I want those patients.  However, if they have had a appointment at any other location, I do not want to see them.

This may sound easy to some, but I cannot figure out how to do it. I can figure out how to see the patients from the list who were seen in other locations, but not if they haven't been.

Is this even possible?

Thank you.
0
Becky Edwards
Asked:
Becky Edwards
  • 6
  • 6
  • 3
  • +1
1 Solution
 
PortletPaulfreelancerCommented:
yes it is possible.

e.g.

SELECT
       patient_id
     , COUNT(case when location  =  'the_one' then  visit_id end)  AS visit_count
FROM all_visits
GROUP BY
       patient_id
HAVING
       SUM(case when location  =  'the_one' then 1 else 0 end)  > 0
     , SUM(case when location <> 'the_one' then 0 else 1 end)  = 0

{+ edit}
that where clause was updated
0
 
PortletPaulfreelancerCommented:
To be more precise on what the query would look like we would need more details on the tables.

---
"sample" data (which can be cleansed!) that represents how your data is structured
 and
"expect result" (that can be derived from the supplied sample)

would enable a much more precise answer.
0
 
mlmccCommented:
Is this all in the same table or tables?

You can do it in Crystal with a subquery

Something like

SELECT mt.patient_Id
FROM myTable  mt
WHERE  Clinic = 'Clinic1' and NOT (mt.patient_id in (SELECT mt2.patientId  FROM mytable mt2 WHERE Clinic <> 'Clinic1''))

mlmcc
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
James0628Commented:
Just to make sure ...
 You're looking at the patients that went to one specific location, right?  You're not looking for patients that only visited a single location, but it could be _any_ location.  You're only interested in the patients that visited location X, and then you only want to see the ones that did not visit any other location besides location X.  Correct?

 Is location X a fixed location, or will you select the location when you run the report (eg. using a parameter)?  I don't know that it matters, but I thought I might as well ask.


 Something in a query, as the others suggested, would be the most efficient solution.  But, FWIW, if you don't have too much data, you might be able to use a "brute force" approach and use group selection in the report.

 You would group on the patient, then go to Report > Selection Formulas > Group and enter something like:

Minimum ({location field}, {patient group field}) = "desired location" and
Maximum ({location field}, {patient group field}) = "desired location"

 The idea is to only select the groups (patients) where the minimum and maximum for the location field are both the location that you're looking for, meaning that the patient didn't have records for any other location.

 But using group selection like that would mean that the report would have to read the records for all locations, so that it would know if a patient had any records for other locations.  Which also means that it would have to read the records for patients that never actually visited the location in question.  So, if you have a lot of data, that may just be too slow.

 And since group selection is really suppression (the report is reading the records for other patients and just not showing them), any normal summaries would include the patients that were not shown on the report.

 James
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
I didn't really understand how Paul is doing it, and I think mlmcc has a solution I can possibly understand and implement.  The group selection one I tried and we have too much data for that one.

So the tables are the same.  I used the appt table to get the specific visits and their corresponding location.  I got a count of distinct patients that were in cardiac.  Now I want a count of those distinct patients who did NOT have any visits from any other location during the same time frame.

So I created a report that shows only patients who were in cardiac, and a subreport of the same patients without the filter for cardiac location.  The subreport is in a group "patientid" and shows a 1 if the patient has an visit in any of the locations that are not cardiac.

So I was going to export this to excel and group by the '1' and then count those patients and subtract them from the total patient count.  I know there has to be an easier way to get the total patients who do NOT have a visit in any other locations.  Plus I need to do this by physician, so I added the physician to the detail level and will do a filter in excel and count them that way.

 I know there has to be an easier way to get the total patients who do NOT have a visit in any other locations, with a count by physician.
0
 
mlmccCommented:
Can you upload the report file?

Are you only concerned about the Cardiac clinic?

If you can't upload the report can you show the queries you used for the reports?

mlmcc
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
No, specifically I need to know which patients who were seen in the cardiac clinic were NOT seen in any other clinic during the year.

Attached is the report.  I have removed patient data.
Appts-Cardiology-forEE.rpt
0
 
mlmccCommented:
The way you are trying to do this won't work because if they didn't have other appointments then the subreport doesn't give the count a value.

Try this report. I added a few formulas but can't test without data.

mlmcc
Appts-Cardiology-forEE.rpt
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Sorry I took so long on this.  We are working on several things at once, as usual!

I tried but it doesn't seem to be right.  Attached is a screen shot after I ran the report.  I think it is showing me all the patients who had a cardiac visit.  I checked and they have visits in other locations too.

What I want is to see only the patients who do not have any other visits besides Cardiac.
Appts-Cardiology-forEE.pdf
0
 
PortletPaulfreelancerCommented:
>>"I didn't really understand how Paul is doing it"

If you are strictly after only those with a SINGLE visit (at a specified location) then you need to COUNT the number of visits.

While I am not a Crystal user I do know is is possible toi use SQL queries in Crystal. (What I can't tell you is how to navigate to the point where SQL is used)

But assuming you find where to put the SQL, the way to do this is along these lines:

SELECT
       patient_id
FROM all_visits

WHERE location = 'the_one' --<< filter for the location can be a parameter
                                                 --<< AND  other filters (e.g. date range)
     
GROUP BY
       patient_id /* this portion in italics just gives a distinct list of patient ID's */


HAVING COUNT(*) = 1  --<< one visit only

The "having clause" is similar to a normal where clause except that now we can evaluate aggregated values such as the result of COUNT()

{+edit}
note max(location) = min(location) will not limit the result to only those with a single visit, it will limit results to those who have only had visits at one location, but that could occur many times.
0
 
James0628Commented:
If you tried the formulas that mlmcc added, he included @xxSubDeclVar in GH1a, with the subreport.  I would remove that formula from GH1a.  It could be resetting the variable to 0 after the subreport runs.

 James
0
 
mlmccCommented:
I think the formula is necessary but what may be happening is Crystal will only evaluate a variable once in a section.

Add another GH1 section between the current GH1a and GH1b.
Move the subreport to that section.

mlmcc
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Can you re-evaluate the report I have attached?  It is pulling correctly.  The 1's showing in Group Header 1a indicate the patient has visits in other locations.  What I need it to be able to put the 1's in gh1b if possible, or filter out the patient's who have 1's and end up with a list of patients who do not have the 1's.

Ultimately - best case would be a total of the patient's who have one's, a total of patient's without 1's, and a total of all patients,
Appts-Cardiology-forEE.rpt
0
 
mlmccCommented:
Take a look at this version.

I added a formula to the subreport - XX
WhilePrintingRecords;
Shared NumberVar VisitCount;
VisitCount := DistinctCount ({PERSON.MEDRECNO}, {PERSON.MEDRECNO});
""

Open in new window


I added a formula to the main report in the report header - Decl
WhilePrintingRecords;
Shared NumberVar VisitCount;
""

Open in new window


I suppressed the GH1a with SUPPRESS BLANK SECTION
I changed the subreport format to
  Turned CAN GROW off - Tab 1
   Turned SUPPRESS BLANK SUBREPORT on - last tab
  I deleted everything from the subreport except the new formula.  Since it isn't going to be displayed no need for other fields.
I suppressed GH1B with a formula in the section expert
WhilePrintingRecords;
Shared NumberVar VisitCount;
VisitCount >= 1

Open in new window



You probably need to be on the DESIGN tab to see the changes since the report now shows nothing

mlmcc
Appts-Cardiology-forEE.rpt
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Let's take this one formula at a time so I can be sure what your intentions are.  The formula XX in the subreport:  This is doing what?  

The formula @decl in the report header of the main report is doing what?

I kind of understand the suppress blank section in GH1a, but don't really know why we need to suppress other than for looks.  The same with suppress blank sub report on the sub report format.  Is this just for looks?

"I suppressed GH1B with a formula in the section expert"
How does the report know what visitcount is?

WhilePrintingRecords;
Shared NumberVar VisitCount;
VisitCount >= 1

So where is my data totaled?  I need totals for # patients with just cardiac visits, # of patients with cardiac and other visits.
0
 
mlmccCommented:
The formula XX in the subreport:
   Used to get the count of visits.  It is just assigning the summary you were displaying to the shared variable.

@decl
  This is just declaring the variable VisitCount as shared so the report knows it exists.
  It creates the placeholder for the value to be passed to the subreport and back.

 Is this just for looks?
  In reality yes.  It depends on what other data will be in the report.  The whitespace it creates might be useful as separation.

How does the report know what visitcount is?
  Since it is declared as shared there is only one memory location for it for the report and the subreport.

So where is my data totaled?
   You haven't asked about this before.  Your issue was in suppressing those with visits other than cardiac.

mlmcc
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Unfortunately I built this for a GE database that I no longer have access to.  However, the solution worked and I was able to give them the answers they needed, along with learning valuable sequel, variables and more information.

Thank you so much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now