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/report_stats.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.5 KB
Line 
1/** report_stats.sql -- Report facts, patients to 3 significant digits
2
3Copyright (c) 2014 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
8SELECT
9  regexp_replace(regexp_substr(m.c_fullname, '([^\\]+).$'), '.$', '') category
10, case
11  when facts > 90000000 then to_char(facts / 1000000, '999') || 'M'
12  when facts > 9000000 then to_char(facts / 1000000, '99.9') || 'M'
13  when facts > 900000 then to_char(facts / 1000000, '9.99') || 'M'
14  when facts > 90000 then to_char(facts / 1000, '999') || 'K'
15  when facts > 9000 then to_char(facts / 1000, '99.9') || 'K'
16  when facts > 900 then to_char(facts / 1000, '9.99') || 'K'
17  else to_char(facts)
18  end facts
19, case
20  when PATIENTS > 90000000 then to_char(PATIENTS / 1000000, '999') || 'M'
21  when PATIENTS > 9000000 then to_char(PATIENTS / 1000000, '99.9') || 'M'
22  when PATIENTS > 900000 then to_char(PATIENTS / 1000000, '9.99') || 'M'
23  when PATIENTS > 90000 then to_char(PATIENTS / 1000, '999') || 'K'
24  when PATIENTS > 9000 then to_char(PATIENTS / 1000, '99.9') || 'K'
25  when PATIENTS > 900 then to_char(PATIENTS / 1000, '9.99') || 'K'
26  else to_char(patients)
27  end patients
28FROM BlueHeronMetadata.CONCEPT_STATS cs
29join (select * from BlueHeronMetadata.heron_terms union all
30      select * from BlueHeronMetadata.redcap_terms_enhanced) m on m.term_id = cs.term_id
31where m.c_hlevel = 1
32order by m.c_name;
Note: See TracBrowser for help on using the repository browser.