inst/QueryLibrary/Aggregate/condition_occurrence/CO03.md

CO03: What is specialty that diagnosed a condition.

Description

Returns the distribution of the physician's specialty who diagnosed a certain condition.

Query

SELECT
  concept_name          AS Specialty,
  specialty_freq        AS Specialty_freq
FROM  
  ( SELECT
      specialty_concept_id,
      COUNT(*)::integer          AS specialty_freq
      FROM
        ( SELECT
            specialty_concept_id,
            from_cond.provider_id
          FROM
            (SELECT
                provider_id
             FROM @cdm.condition_occurrence
             WHERE condition_concept_id = $1
                   AND provider_id IS NOT NULL
            ) AS from_cond
          LEFT JOIN
            ( SELECT
                provider_id       AS provider_id_from_prov,
                specialty_concept_id
              FROM @cdm.provider
            ) AS from_prov
          ON from_cond.provider_id=from_prov.provider_id_from_prov
        ) AS prov_cond_spec
      GROUP BY specialty_concept_id
  ) AS spec_id_count
LEFT JOIN
  (SELECT
    concept_id,
    concept_name
   FROM @vocab.concept
  ) AS spec_concept
ON spec_id_count.specialty_concept_id = spec_concept.concept_id
ORDER BY specialty_freq DESC;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | condition_concept_id | 31967 | Yes | Condition concept ID for 'Nausea' | |

Output

| Field | Description | | --- | --- | | Specialty | Physician's specialty. | | Specialty_freq | Frequency of the specialty recording medical condition |

Example output record

| Field | Description | | --- | --- | | Specialty | Internal Medicine | | Specialty_freq | 10324 |

Documentation

https://github.com/OHDSI/CommonDataModel/wiki/



sib-swiss/dsQueryLibraryServer documentation built on Feb. 13, 2025, 8:07 p.m.