DISTINCT problem

[dugjohnson]

I need to do a mess of cross tabs based on whether a patient has had services in a given period. And while I am at it, I am counting their family members in the crosstab too. By linking the exam table to the patient table, then linking the patient table to itself as an alias via the family number (patient ID and family number are different fields) I get the records. Wahoo. But if I have multiple exams for a patient in the period, my count is multiplied by that amount. What I really want to count on is the distinct records in the final patient/family member table, but I can't figure out how to make just that table distinct.
The SQL currently looks like
SELECT
PATIENT."SEX", PATIENT."ZIP", PATIENT."FIN_TYPE", PATIENT."ZZETHNICTY", PATIENT."ZZHANDICAP", PATIENT."ZZLANGUAGE", PATIENT."ZZRELGION", PATIENT."ZZINCOME", PATIENT."ZZINSTYPE",
EXAM."EXAMDATE"
FROM
"PATIENT" PATIENT,
"PATIENT" Patient2,
"EXAM" EXAM
WHERE
PATIENT."BILLNUM" = Patient2."BILLNUM" AND
Patient2."PAT_ID" = EXAM."PAT_ID" AND
EXAM."EXAMDATE" >= '20030701' AND
EXAM.&quot;EXAMDATE&quot; <= '20030731'

Patient2 is the linking patient table, Patient is the one I am counting.
Any help would be appreciated.
Doug

 

[pgtek]

Hi
Try this
SELECT distinct
PATIENT.&quot;SEX&quot;, PATIENT.&quot;ZIP&quot;, PATIENT.&quot;FIN_TYPE&quot;, PATIENT.&quot;ZZETHNICTY&quot;, PATIENT.&quot;ZZHANDICAP&quot;, PATIENT.&quot;ZZLANGUAGE&quot;, PATIENT.&quot;ZZRELGION&quot;, PATIENT.&quot;ZZINCOME&quot;, PATIENT.&quot;ZZINSTYPE&quot;,
EXAM.&quot;EXAMDATE&quot;
FROM
&quot;PATIENT&quot; PATIENT,
&quot;PATIENT&quot; Patient2,
&quot;EXAM&quot; EXAM
WHERE
PATIENT.&quot;BILLNUM&quot; = Patient2.&quot;BILLNUM&quot; AND
Patient2.&quot;PAT_ID&quot; = EXAM.&quot;PAT_ID&quot; AND
EXAM.&quot;EXAMDATE&quot; >= '20030701' AND
EXAM.&quot;EXAMDATE&quot; <= '20030731'



pgtek

 

[dugjohnson]

Sorry, I didn't mention that I did that. I get a record for each patient AND exam date, so that if the patient is seen twice in a period, there are two records that get counted. I need to count 1 for each patient that is seen in a period, no matter how many times they were seen.
I'm thinking a select from a select, but haven't been able to put it together yet.

 

[Madawc]

In Crystal 8.5, 'distinct count' is an option for both running totals and summary totals. So if you have nine apples, two bananas and one cherry, it counts 3 for three distinct types of fruit. Does that solve it?

Madawc Williams
East Anglia, Great Britain