Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/count_facts_by_concept.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 1.2 KB
Line 
1/** count_facts_by_concept.sql -- summarize facts by concept
2
3Copyright (c) 2012 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7*/
8
9alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
10-- set timing on;
11-- set echo on;
12select sysdate from dual;
13
14/****
15 * Environment Checks
16 *
17 * Check that we're running in a de-id repository
18 */
19select c_table_cd from BlueHeronMetadata.table_access where 1 = 0;
20
21
22/******
23 * Compute count of facts by concept.
24 */
25whenever sqlerror continue;
26drop table BlueHeronMetadata.counts_by_concept;
27drop index BlueHeronMetadata.count_concept_cd;
28whenever sqlerror exit;
29
30create table BlueHeronMetadata.counts_by_concept as
31select f.concept_cd, count(*) facts, count(distinct patient_num) patients
32     , max(import_date) import_date -- keep track of when we cached these data.
33 from BlueHeronData.observation_fact f
34 group by f.concept_cd;
35/* Elapsed: 00:37:32.076 */
36;
37
38/* eyeball it:
39select * from BlueHeronMetadata.counts_by_concept
40order by facts desc;
41*/
42
43create index BlueHeronMetadata.count_concept_cd
44 on BlueHeronMetadata.counts_by_concept (concept_cd);
45
Note: See TracBrowser for help on using the repository browser.