How to filter out patients not seen at other locations

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

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.

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

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

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.

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

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

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

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

>>"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.

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

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

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

Take a look at this version.

I added a formula to the subreport - XX

WhilePrintingRecords;
Shared NumberVar VisitCount;
VisitCount := DistinctCount ({PERSON.MEDRECNO}, {PERSON.MEDRECNO});
""

Select allOpen in new window

I added a formula to the main report in the report header - Decl

WhilePrintingRecords;
Shared NumberVar VisitCount;
""

Select allOpen 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

Select allOpen 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

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.

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!