CODEWITHSUNDEEP
sqlcrystal-reportscrosstab
4444
4444

Reputation: 3680

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"

Upvotes: 1

Views: 171

Answers (2)

4444
4444

Reputation: 3680

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 "Entry2"."DiagnosisRecord"="Record2"."ID" AND
          "Entry2"."EPISODE_NUMBER"="Record2"."EPISODE_NUMBER" AND
          "Entry2"."FACILITY"="Record2"."FACILITY" AND
          "Entry2"."PATID"="Record2"."PATID" AND
          "Record"."PATID"="Record2"."PATID"
)

The key differences being:

  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 now it checks for the latest diagnosis per person.

Upvotes: 0

muffaddal shakir
muffaddal shakir

Reputation: 81

You need only the latest diagnosis among a set of diagnoses. So I would suggest:

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 "Entry"."date_of_diagnosis" = (SELECT MAX("date_of_diagnosis") FROM      
   "DiagonsisRecord" "A" WHERE "A"."DiagnosisRecord"="Entry"."DiagnosisRecord"  )

This should get the maximum Date_of_Diagnosis for each patient and pass the filter parameter to get the last diagnosis of that patient.

Upvotes: 1

Related Questions