4444 4444 - 1 month ago 11
SQL Question

Ignore suppressed records in crosstab

My report shows only the latest diagnosis per patient based on their

date_of_diagnosis
- all other records are suppressed:

Records after suppression

I summarize by diagnosis and age group in a crosstab. Crosstabs evaluate before printing, so any attempts to suppress, share variables, or summarize happen after the crosstab populates. This means Total in Each Age Group is correct, because each patient only has one age - but if a patient has more than one diagnosis, even if they're suppressed, they get counted multiple times:

Crosstab (Much larger in real life. Simplified/cropped for screenshot)

I absolutely must use a crosstab for this due to the large number of diagnoses and age groups involved. How can I get the crosstab to ignore suppressed records? Or if I need to use a custom SQL Command table, how can I rewrite the existing SQL to ignore obsolete records?




Crystal's auto-generated SQL (through ODBC):

SELECT "Codes"."diagnosis_code",
"Codes"."diagnosis_value",
"Codes"."PATID",
"Codes"."FACILITY",
"Codes"."EPISODE_NUMBER",
"Record"."date_of_diagnosis"

FROM "SYSTEM"."Codes" "Codes",
"SYSTEM"."Entry" "Entry",
"SYSTEM"."Record" "Record"

WHERE "Codes"."DiagnosisEntry"="Entry"."ID" AND
"Codes"."EPISODE_NUMBER"="Entry"."EPISODE_NUMBER" AND
"Codes"."FACILITY"="Entry"."FACILITY" AND
"Codes"."PATID"="Entry"."PATID" AND
"Entry"."DiagnosisRecord"="Record"."ID" AND
"Entry"."EPISODE_NUMBER"="Record"."EPISODE_NUMBER" AND
"Entry"."FACILITY"="Record"."FACILITY" AND
"Entry"."PATID"="Record"."PATID"

Answer

Building off of Muffaddal Shakir's answer, I was able to write this query to perform the correct filter:

SELECT "Codes"."PATID",
   "Codes"."diagnosis_code",
   "Codes"."diagnosis_value",
   "Codes"."FACILITY",
   "Codes"."EPISODE_NUMBER",
   "Record"."date_of_diagnosis"

FROM   "SYSTEM"."codes" "Codes",
   "SYSTEM"."entry" "Entry",
   "SYSTEM"."record" "Record"

WHERE  "Codes"."DiagnosisEntry"="Entry"."ID" AND
   "Codes"."EPISODE_NUMBER"="Entry"."EPISODE_NUMBER" AND
   "Codes"."FACILITY"="Entry"."FACILITY" AND
   "Codes"."PATID"="Entry"."PATID" AND
   "Entry"."DiagnosisRecord"="Record"."ID" AND
   "Entry"."EPISODE_NUMBER"="Record"."EPISODE_NUMBER" AND
   "Entry"."FACILITY"="Record"."FACILITY" AND
   "Entry"."PATID"="Record"."PATID"

AND "Record"."date_of_diagnosis" = (   
    SELECT MAX("Record2"."date_of_diagnosis")
    FROM
       "SYSTEM"."entry" "Entry2",
       "SYSTEM"."record" "Record2"
    WHERE
       "Record2"."ID"="Entry2"."DiagnosisRecord" AND
       "Entry2"."EPISODE_NUMBER"="Record2"."EPISODE_NUMBER" AND
       "Entry2"."FACILITY"="Record2"."FACILITY" AND
       "Entry2"."PATID"="Record2"."PATID" AND
       "Record"."PATID"="Record2"."PATID"
)

There are two main differences between our answers: 1. The subquery uses unique aliases from the main query. 2. The last line "Record"."PATID"="Record2"."PATID" - Without this, the query only pulls back one diagnosis (the latest one in the whole system.) But with this line it only checks for the latest diagnosis per person.