Query check ICD10 trach dxs consistent

Data Integrity Checks
Summary:	Tracheostomy related CCI and ICD10 codes must be consistent with each other.
Related:	Tracheostomy, has one, Tracheostomy care, Tracheostomy complication, hemorrhage from site, Tracheostomy complication, malfunction, Tracheostomy complication, NOS, Tracheostomy complication, tracheo-esophageal fistula, Tracheostomy creation
Firmness:	hard check
Timing:
App:	CCMDB.mdb
Coding:	Query check_ICD10_trach_dxs_consistent
Uses L Problem table:	not relevant for this app
Status:	implemented
Implementation Date:	2019-01-22
Backlogged:	true

Cargo

SMW

"CCMDB.mdb" is not in the list (SAP, not entered, Centralized data front end.accdb, DSM Labs Consistency check.accdb, CCMDB.accdb, TISS28.accdb) of allowed values for the "DICApp" property.

Categories:

form:

if any of following dxs are present then must have Tracheostomy, has one (Z93.0):
- ICD10
  - Tracheostomy complication, hemorrhage from site (J95.00)
  - Tracheostomy complication, malfunction (J95.02)
  - Tracheostomy complication, NOS (J95.08)
  - Tracheostomy complication, tracheo-esophageal fistula (J95.03)
- CCI
  - Tracheostomy creation

old query SQL

click to expand old query

SELECT l_log.pat_id,

      has_trach_implying_code.pat_id,
      has_hasTrach_code.pat_id,
      *

FROM ((SELECT l_log.pat_id,

              l_cci_picklist.cci_picklist_code,
              l_icd10.icd10_code
       FROM   (l_log
               INNER JOIN l_cci_picklist
                       ON l_log.pat_id = l_cci_picklist.pat_id)
              INNER JOIN l_icd10
                      ON l_log.pat_id = l_icd10.pat_id
       WHERE  (( ( l_icd10.icd10_code ) = "j95.00"
                  OR ( l_icd10.icd10_code ) = "j95.02"
                  OR ( l_icd10.icd10_code ) = "j95.08"
                  OR ( l_icd10.icd10_code ) = "j95.03" ))
               OR (( ( l_cci_picklist.cci_picklist_code ) = "1.gj.77" ))) AS
      has_trach_implying_code
       RIGHT JOIN l_log
               ON has_trach_implying_code.pat_id = l_log.pat_id)
      LEFT JOIN (SELECT l_icd10.pat_id,
                        l_icd10.icd10_code
                 FROM   l_icd10
                 WHERE  (( ( l_icd10.icd10_code ) = "z93.0" ))) AS
                has_hasTrach_code
             ON l_log.pat_id = has_hasTrach_code.pat_id

WHERE ( ( ( l_log.pat_id ) = [param] )

        AND ( ( has_trach_implying_code.pat_id ) IS NOT NULL )
        AND ( ( has_hasTrach_code.pat_id ) IS NULL ) );

Log

2019-04-08 - removed Tracheostomy care from this query
2019-03-20 - previously this also required one of the above if "has one" was present, but that makes no sense, because one could be present without additional care requirements, so have taken that part out. Ttenbergen 12:45, 2019 March 20 (CDT)