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/epic_meds_transform.sql

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

Merge with demo_concepts_3800

File size: 41.9 KB
Line 
1/** epic_meds_transform: EPIC to 12b2 ETL: transform meds
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
7For an overview, see http://informatics.kumc.edu/work/wiki/HeronLoad
8see also epic_i2b2_transform.sql
9
10We assume the following permissions:
11 grant create view to &me;
12 grant select any table to &me;
13
14*/
15
16/* Check that we're connected to an Epic CLARITY database.
17   */
18select pat_id from CLARITY.patient where 1 = 0;
19
20
21/*************************
22Medication dispense facts
23
24All dispense records are modifiers to an order: no dispense exists that doesn't
25have an order.
26
27Order ids are unique, but there could be (very likely are) multiple dispense
28records for a single order.  Therefore, we'll construct and instance id from
29various parts of the line, contact_date_real, and med_id.
30
31Note that instance_num only has 18 digits allowed.
32
33Construct a unique instance_num using:
34   1 digit for type (see below)
35   2 digits for line
36   2 digits for contact_date_real (right of decimal portion)
37   13 digits for order_med_id (could steal digits from here?)
38 
39Type (the 1 digit for type above)
40  1 = Order
41  2 = Dispense
42  3 = Administration
43 
44Primary key of the order_disp_meds table:
45  ORDER_MED_ID
46  CONTACT_DATE_REAL
47  LINE
48
49ORDER_MED_ID:
50  Unique ID of the medication order
51
52CONTACT_DATE_REAL
53  Days since December 31st 1840 00:00:00 (http://en.wikipedia.org/wiki/MUMPS).
54  We believe that the part of this date to the right of the decimal point
55  (.01, .02, .03) are the administrations for the day (multiple dispenses).
56  This was verified from Epic documentation (Clarity All Tables and Columns).
57
58LINE
59  count of dispenses within a contact.
60
61We can test our assumptions about the number of digits in ORDER_MED_ID
62and LINE, but for CONTACT_DATE_REAL, we'll just assume 2 digits suffices.
63(Famous last words.)
64*/
65
66create or replace view etl_test_domain_meds as
67select 'Medications' test_domain from dual;
68
69insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
70with test_key as (
71  select test_domain,
72  'med_disp_assumption_line' test_name from etl_test_domain_meds
73  ),
74max_line as (
75  select max(line) test_value from clarity.order_disp_meds
76  )
77select max_line.*, test_key.*, sq_result_id.nextval, sysdate
78from max_line, test_key;
79commit;
80
81insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
82with test_key as (
83  select test_domain,
84  'med_disp_orderid_digits' test_name from etl_test_domain_meds
85  ),
86max_digits as (
87  select max(order_med_id) test_value from clarity.order_disp_meds
88  )
89select max_digits.*, test_key.*, sq_result_id.nextval, sysdate
90from max_digits, test_key;
91commit;
92
93--Our test creation of the instance_num
94/*
95select codm.order_med_id, codm.line, codm.contact_date_real,
96  (2 +
97   1e1 * (codm.line +
98          1e2 * mod((codm.contact_date_real * 100), 100) +
99          1e4 * codm.order_med_id )) inum
100from
101  clarity.order_disp_meds codm;
102*/
103
104/*
105--Just to prove it works for maximum sizes
106select
107  (2 +
108   1e1 * (d.line +
109          1e2 * d.contact_date_real +
110          1e4 * d.order_med_id ) ) inum
111from
112  clarity.order_disp_meds codm,
113    (
114    select 9999999999999 order_med_id, 88 contact_date_real, 77 line from dual
115    ) d;
116--results in 999999999999988772
117*/
118
119--Is it rare to get something like 990?  Yes - only 7/30559156
120/*
121select
122  mod((contact_date_real  * 1000 ),1000) val
123from
124  CLARITY.order_disp_meds
125order by val desc;
126*/
127
128/* Some dispense records only have the medication id included in the dispense
129record and not the order record.  So, the two tests below make sure that all
130medication facts (dispenses and all order types) will have a medication id (and
131therefore ensure all medication facts have known concepts).
132*/
133
134insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
135with test_key as (
136  select 'Medications' test_domain,
137  'few_null_disp_ids' test_name from dual
138  )
139, null_disp_meds as (
140  select count(*) n from clarity.order_disp_meds where disp_med_id is null
141  )
142, denom as (
143  select count(*) n from clarity.order_disp_meds
144  )
145, pct_null_meds as (
146  select (null_disp_meds.n / denom.n) * 100 pct_null
147  from null_disp_meds, denom
148  )
149select pct_null, test_domain, test_name, sq_result_id.nextval, sysdate
150from pct_null_meds, test_key
151;
152commit;
153
154insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
155with test_key as (
156  select test_domain,
157  'all_med_orders_have_ids' test_name from etl_test_domain_meds
158  ),
159dates_missing_med_id as (
160  select count(*) test_value from (
161    select distinct trunc(update_date) from clarity.order_med com
162    left join clarity.order_disp_meds codm
163    on com.order_med_id = codm.order_med_id
164    where com.medication_id is null and codm.disp_med_id is null
165    )
166  )
167select dates_missing_med_id.*, test_key.*, sq_result_id.nextval, sysdate
168from dates_missing_med_id, test_key;
169commit;
170
171/*
172Modifier:  Add a row for every medication fact added above to indicate that it
173is a "dispense".  If we had a fact with multiple modifiers attached to it, the
174instance number for the modifier entry MUST match the associated row in the fact
175table. Refer to the i2b2 community page regarding modifiers:
176https://community.i2b2.org/wiki/display/DevForum/Modifiers+in+i2b2+Data+Model.
177*/
178create or replace view kuh_med_disp_mods as
179select distinct
180  to_char(com.pat_enc_csn_id) encounter_ide,
181  mod(com.order_med_id, &&heron_etl_chunks)+1 as part,
182  com.pat_id patient_ide,
183  -- TODO: reference scheme constants from metadata (see metadata_init.sql)
184  'KUH|MEDICATION_ID:' || codm.disp_med_id concept_cd,
185  '@' PROVIDER_ID,
186  codm.contact_date start_date,
187  -- TODO: reference scheme constants from metadata (see metadata_init.sql)
188  'MedObs:Dispensed' modifier_cd,
189  (2 +
190   1e1 * (codm.line +
191          1e2 * mod((codm.contact_date_real * 100), 100) +
192          1e4 * codm.order_med_id )) instance_num,
193  'N' valtype_cd,
194  '@' tval_char,
195  codm.disp_qty nval_num, -- packages is sometimes null, but disp_qty never is
196  '@' valueflag_cd,
197  -- quantity_num ?
198  -- instance_num ?
199  munit.abbr units_cd,
200  case when com.order_end_time is not null then com.order_end_time
201       else codm.contact_date
202  end end_date,
203  null location_cd,
204  to_number(null) confidence_num,
205  com.update_date update_date
206from CLARITY.order_disp_meds codm
207  left join CLARITY.zc_med_unit munit on munit.disp_qtyunit_c = codm.disp_qtyunit_c 
208  join CLARITY.order_med com on com.order_med_id=codm.order_med_id 
209  join clarity.pat_enc cpe on cpe.pat_enc_csn_id = com.pat_enc_csn_id
210where codm.disp_med_id in
211  ( select medication_id from clarity.clarity_medication ); 
212
213
214/****************************************
215Medication order facts
216
217Construct a unique instance_num similar to the the way we created it for
218medication dispense facts:
219   1 digit for type (see below)
220   4 digits unused (keep digit position consistent with dispense facts)
221   13 digits for order_med_id (could steal digits from here?)
222 
223Type (the 1 digit for type above)
224  1 = Order
225  2 = Dispense
226  3 = Administration
227 
228Primary key of the order_med table:
229  ORDER_MED_ID - Unique ID of the medication order - this cannot be null.
230
231There are order_med entries that that don't have medication_ids associated with
232them.  In these cases, we have a dispense record that refers to an existing
233order.  So, we may have multiple medication_ids associated with a single order.
234We can therefore get the medications from clarity.order_disp_meds.disp_med_id
235by linking with the order_med_id.
236*/
237
238insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
239with test_key as (
240  select test_domain,
241  'med_orderid_digits' test_name from etl_test_domain_meds
242  ),
243max_order_id as (
244  select max(order_med_id) test_value from clarity.order_med
245  )
246select max_order_id.*, test_key.*, sq_result_id.nextval, sysdate
247from max_order_id, test_key;
248commit;
249
250
251--Our test creation of the instance_num
252/*
253select com.order_med_id,
254  (1 +
255   1e1 * ( 1e4 * com.order_med_id )) inum
256from
257  clarity.order_med com;
258*/
259
260/*
261--Just to prove it works for maximum sizes
262select
263  (1 +
264   1e1 * ( 1e4 * d.order_med_id ) ) inum
265from
266  clarity.order_med com,
267    (
268    select 9999999999999 order_med_id from dual
269    ) d;
270--results in 999999999999900001
271*/
272
273/*
274The order_id_to_med_id table commented below might be useful for debugging as
275this is what I'm trying to accomplish with the 'distinct' keywoad in the
276kuh_med_orders view below.  I want the distinct combination of order_id and
277medication id.
278*/
279
280/*
281create table order_id_to_med_id as(
282select distinct
283  order_id,
284  case
285    when com_id is not null then com_id
286    when codm_id is not null then codm_id
287  end medication_id
288from(
289  select
290    com.order_med_id order_id, codm.disp_med_id codm_id, com.medication_id com_id
291  from
292    clarity.order_med com
293  left join
294    clarity.order_disp_meds codm
295  on
296    com.order_med_id = codm.order_med_id
297  left join
298    clarity.clarity_medication ccm
299  on
300    codm.disp_med_id = ccm.medication_id
301));
302*/
303
304whenever sqlerror continue;
305/* Previously, this was a view but we changed to a table for performance
306concerns.  See #3586.
307*/
308drop view med_order_to_med_id;
309drop table med_order_to_med_id;
310whenever sqlerror exit;
311
312create table med_order_to_med_id as
313-- Distinct since multiple dispenses can be associated with a single order
314select distinct orders.* from (
315  select com.order_med_id, coalesce(info.medication_id, com.medication_id, codm.disp_med_id) medication_id
316  from clarity.order_med com
317  left join clarity.order_medinfo info on info.order_med_id = com.order_med_id
318  left join clarity.order_disp_meds codm on com.order_med_id = codm.order_med_id
319  ) orders
320-- Filter out unknown medication ids with inner join (#1174)
321join clarity.clarity_medication cmed on cmed.medication_id = orders.medication_id
322;
323
324
325/* Note that the facts from this table aren't actually loaded into the
326observation_fact table.  Instead, it's used to build the modifier entries later.
327*/
328
329whenever sqlerror continue;
330/* Previously, this was a view but we changed to a table for performance
331concerns.  See #3586.
332*/
333drop view kuh_med_orders;
334drop table kuh_med_orders;
335whenever sqlerror exit;
336
337create table kuh_med_orders (
338  encounter_ide     VARCHAR2(200 BYTE),
339  part              NUMBER,
340  patient_ide       VARCHAR2(200 BYTE),
341  concept_cd        VARCHAR2(50 BYTE),
342  provider_id       VARCHAR2(50 BYTE),
343  start_date        DATE,
344  modifier_cd       VARCHAR2(100 BYTE),
345  instance_num      NUMBER(18,0),
346  valtype_cd        VARCHAR2(50 BYTE),
347  tval_char         VARCHAR2(4000 BYTE),
348  nval_num          NUMBER(18,5),
349  valueflag_cd      VARCHAR2(50 BYTE),
350  units_cd          VARCHAR2(50 BYTE),
351  end_date          DATE,
352  location_cd       VARCHAR2(50 BYTE),
353  confidence_num    NUMBER(18,5),
354  update_date       DATE,
355  order_class_c     NUMBER(38,0),
356  ordering_mode     VARCHAR2(255 BYTE),
357  ordering_mode_c   NUMBER(38,0),
358  order_med_id      NUMBER(18,0)
359  );
360
361insert into kuh_med_orders
362select
363  to_char(com.pat_enc_csn_id) encounter_ide,
364  mod(com.order_med_id, &&heron_etl_chunks)+1 as part,
365  com.pat_id patient_ide,
366  'KUH|MEDICATION_ID:' || otom.medication_id concept_cd,
367  '@' provider_id,
368    case
369      when com.order_start_time is not null then com.order_start_time --Date/time when med order is to start
370      when com.start_date is not null then com.start_date --The date when the medication order started
371      when com.order_inst is not null then order_inst --Date/time order was placed
372      when com.ordering_date is not null then com.ordering_date --The date when the medication order was placed
373    end start_date,
374  '@' modifier_cd,
375  --Note description above for the instance number (Medication dispense facts)
376  (1 + 1e1 * ( 1e4 * com.order_med_id )) instance_num,
377  null valtype_cd,
378  case
379    when com.hv_discrete_dose is not null then com.hv_discrete_dose
380    else '@'
381  end tval_char,
382  to_number(null) nval_num,
383  '@' valueflag_cd,
384  /* Using the "lower" function to make sure the unit matches the dose modifiers.
385  Note that lower(null) is still null */
386  lower(mu.name) units_cd,
387  /* If medication orders don't have an end date recorded, set the end date to
388  the death date or sysdate.  See #3586.
389  */
390  coalesce(com.order_end_time, pd.death_date, sysdate) end_date,
391  null location_cd,
392  to_number(null) confidence_num,
393  com.update_date update_date,
394  com.order_class_c as order_class_c,
395  com.ordering_mode as ordering_mode,
396  com.ordering_mode_c as ordering_mode_c,
397  com.order_med_id
398from
399  CLARITY.order_med com
400join med_order_to_med_id otom on otom.order_med_id = com.order_med_id
401join nightherondata.patient_mapping pm on pm.patient_ide = com.pat_id
402join nightherondata.patient_dimension pd on pd.patient_num = pm.patient_num
403left join
404  clarity.zc_med_unit mu on mu.disp_qtyunit_c = com.hv_dose_unit_c
405;
406
407create index kuh_med_orders_id_idx on kuh_med_orders(order_med_id);
408create index kuh_med_orders_class_idx on kuh_med_orders(order_class_c);
409create index kuh_med_orders_mode_idx on kuh_med_orders(ordering_mode);
410create index kuh_med_orders_modec_idx on kuh_med_orders(ordering_mode_c);
411
412
413/*
414Modifier:  Historical/Other Order
415Add a row for every medication order that is "historical".  This is referred to
416as a "home medication" or PTA (prior-to-admission).  The instance number for the
417modifier entry MUST match the associated row in the fact table.  Refer to the
418i2b2 community page regarding modifiers:
419https://community.i2b2.org/wiki/display/DevForum/Modifiers+in+i2b2+Data+Model.
420
421Select everything from our view we just created - if it's historical, then just
422add another row from the view (to ensure everything is kept the same) but change
423the modifier field to indicate that it's an historical medication.  Otherwise,
424add the modifier "Other".
425*/
426
427create or replace view kuh_historical_other_med_mods as
428select
429  medo.encounter_ide, medo.part,
430  medo.patient_ide, medo.concept_cd, medo.provider_id, medo.start_date, 
431  case
432    --Refer CLARITY.zc_order_class
433    when medo.order_class_c = 3 then 'MedObs:Historical'
434    else 'MedObs:Other'
435  end modifier_cd,
436  medo.instance_num, medo.valtype_cd, medo.tval_char, medo.nval_num, medo.valueflag_cd, medo.units_cd, medo.end_date,
437  medo.location_cd, medo.confidence_num, medo.update_date
438from
439  kuh_med_orders medo
440left join
441  CLARITY.zc_order_class czoc
442on
443  medo.order_class_c = czoc.order_class_c;
444
445
446/*
447Modifier: Ambulatory vs. Inpatient
448
449Note the following regarding the ordering_mode field from clarity documentation:
450
451This column is deprecated. The column previously extracted the category title.
452In order to avoid data consistency issues, use ORDERING_MODE_C to link to
453ZC_ORDERING_MODE instead. 
454*/
455
456/*
457Make sure that we know what almost all of the orders are.  Threshold chosen
458somewhat arbitrarily. 
459*/
460insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
461with test_key as (
462  select test_domain,
463  'most_med_orders_have_order_mode' test_name from etl_test_domain_meds
464  )
465select
466  (no_mode/(select count(*) from clarity.order_med)) * 100 test_value, test_key.*,
467  sq_result_id.nextval, sysdate
468from test_key,
469  (
470  select
471    count(*) as no_mode
472  from
473    clarity.order_med com
474  where
475    ordering_mode_c is null and ordering_mode is null
476  );
477commit;
478
479/*
480--Count of each type - see what the above is testing
481select count(*), ordering_mode_c, ordering_mode from clarity.order_med
482group by ordering_mode_c, ordering_mode;
483*/
484
485/*
486TODO: Consider referencing clarity.zc_ordering_mode rather than numbers/strings
487below.  I've added the table to the clarity_table_extract_script.txt for the
488next load (#1040).
489*/
490
491create or replace view kuh_inp_outp_med_mods as
492select
493  medo.encounter_ide, medo.part,
494  medo.patient_ide, medo.concept_cd, medo.provider_id, medo.start_date, 
495  case
496    --Refer to clarity.zc_ordering_mode
497    when medo.ordering_mode = 'Outpatient' or medo.ordering_mode_c = 1 then 'MedObs:Outpatient'
498    when medo.ordering_mode = 'Inpatient' or medo.ordering_mode_c = 2 then 'MedObs:Inpatient'
499  end modifier_cd,
500  medo.instance_num, medo.valtype_cd, medo.tval_char, medo.nval_num, medo.valueflag_cd, medo.units_cd, medo.end_date,
501  medo.location_cd, medo.confidence_num, medo.update_date
502from
503  kuh_med_orders medo
504where
505  /*
506  Only add the modifier if we have expected values.  The test above should make
507  sure that almost all rows have the needed field.
508  */
509  (medo.ordering_mode='Outpatient' or medo.ordering_mode='Inpatient'
510  or medo.ordering_mode_c = 1 or medo.ordering_mode_c = 2 );
511
512
513/* PRN modifier
514*/
515create or replace view kuh_prn_med_mods as (
516select distinct
517  medo.encounter_ide, medo.part, medo.patient_ide, medo.concept_cd, medo.provider_id,
518  medo.start_date, 'MedObs:PRN' modifier_cd,  medo.instance_num, medo.valtype_cd,
519  medo.tval_char, medo.nval_num, medo.valueflag_cd, medo.units_cd, medo.end_date,
520  medo.location_cd, medo.confidence_num, medo.update_date, medo.order_med_id
521from kuh_med_orders medo
522join clarity.order_med com on com.order_med_id = medo.order_med_id
523join clarity.ip_frequency freq on freq.freq_id=com.hv_discr_freq_id
524where freq.prn_yn = 'Y'
525);
526
527/*************************
528Medication administration facts
529
530These are from the Medication Administration Record (MAR).  There can be multiple
531MAR records for a single order.
532
533As per Clarity documentation, the primary key of the clarity.mar_admin_info table
534is the order_med_id + line.
535
536Similar to orders, construct a unique instance_num using:
537   1 digit for type (see below)
538   5 digits for line (max observed line is 19,xxx)
539   12 digits for order_med_id (could steal digits from here?)
540 
541Type (the 1 digit for type above)
542  1 = Order
543  2 = Dispense
544  3 = Administration
545*/
546
547-- Test assumption noted above: 5 digits for line
548insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
549with test_key as (
550  select test_domain,
551  'med_mar_assumption_line' test_name from etl_test_domain_meds
552  ),
553max_digits as (
554  select max(line) test_value from clarity.mar_admin_info
555  )
556select max_digits.*, test_key.*, sq_result_id.nextval, sysdate
557from max_digits, test_key;
558commit;
559
560insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
561with test_key as (
562  select test_domain,
563  'med_mar_orderid_digits' test_name from etl_test_domain_meds
564  ),
565max_digits as (
566  select max(order_med_id) test_value from clarity.mar_admin_info
567  )
568select max_digits.*, test_key.*, sq_result_id.nextval, sysdate
569from max_digits, test_key;
570commit;
571
572--For admin records that have an action other than null, make sure we have a date.
573select case when num = 0 then 1 else 1/0 end as med_mar_time_null from
574  (
575  select count(*) num from(
576    select * from clarity.mar_admin_info
577    where (mar_action_c is not null and taken_time is null)
578    )
579  );
580
581--Our test creation of the instance_num
582/*
583select cmar.order_med_id, cmar.line,
584  (3 +
585   1e1 * (cmar.line +
586          1e5 * cmar.order_med_id )) inum
587from
588  clarity.mar_admin_info cmar;
589
590--Just to prove it works for maximum sizes
591select
592  (3 +
593   1e1 * (d.line +
594          1e5 * d.order_med_id ) ) inum
595from
596  clarity.mar_admin_info cmar,
597    (
598    select 999999999999 order_med_id, 88888 line from dual
599    ) d;
600--results in 999999999999888883
601*/
602
603-- Create instance number for MAR records
604create or replace view mar_admin_instance as
605select
606  mar.line, mar.order_med_id, (3 + 1e1 * (mar.line + 1e5 * mar.order_med_id )) instance_num
607from clarity.mar_admin_info mar;
608
609/* Note:  As before, these aren't loaded as facts but the view helps reduce code
610since we may have more than one modifier on the same facts.
611*/
612create or replace view kuh_med_mar_mods as(
613  select distinct
614    to_char(cmar.mar_enc_csn) encounter_ide,
615    mod(cmar.order_med_id, &&heron_etl_chunks)+1 as part,
616    cpe.pat_id patient_ide,
617    -- TODO: reference scheme constants from metadata (see metadata_init.sql)
618    'KUH|MEDICATION_ID:' || otom.medication_id concept_cd,
619    '@' PROVIDER_ID,
620    cmar.taken_time start_date,
621    -- TODO: reference scheme constants from metadata (see metadata_init.sql)
622    'MedObs|MAR:' || cmr.name modifier_cd,
623    inst.instance_num,
624    '@' valtype_cd,
625    '@' tval_char,
626    to_number(null) nval_num,
627    '@' valueflag_cd,
628    null units_cd,
629    cmar.taken_time end_date,
630    null location_cd,
631    to_number(null) confidence_num,
632    cmar.saved_time update_date
633  from CLARITY.mar_admin_info cmar
634  join CLARITY.pat_enc cpe on cpe.pat_enc_csn_id=cmar.mar_enc_csn
635  join med_order_to_med_id otom on otom.order_med_id = cmar.order_med_id
636  join clarity.zc_mar_rslt cmr on cmar.mar_action_c = cmr.result_c
637  join mar_admin_instance inst on inst.order_med_id = cmar.order_med_id
638    and inst.line = cmar.line
639  where cmar.mar_action_c is not null
640); 
641
642
643/* MAR: How many records do we have for each result type?
644with counts as(
645  select count(*) cnt, f.mar_action_c
646  from kuh_med_mar_facts f
647  group by f.mar_action_c
648  )
649select r.result_c, r.name, counts.cnt
650from clarity.zc_mar_rslt r
651left join counts
652on r.result_c = counts.mar_action_c
653order by counts.cnt desc;
654*/-- select count(*) from kuh_med_mar_facts;
655
656
657/* Daily dose
658https://community.i2b2.org/wiki/display/DevForum/Metadata+XML+for+Medication+Modifiers:
659In versions 1.6 RC4 and above, the Units_CD column is used to specify several
660possible values for the units. Conversions will occur between units as long as
661they are specified in the Metadata_XML file.  It should be recognized however
662that queries may run considerable slower when specifying values in this mode.
663
664TODO: Consider dose ranges (like 0.5-1 mg)?
665*/
666
667
668insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
669with test_key as (
670  select test_domain,
671  'du_over_hv' test_name from etl_test_domain_meds
672  ),
673hv_dose_null as (
674  select count(*) test_value
675  from clarity.order_med
676  where hv_dose_unit_c is null and dose_unit_c is not null
677  )
678select hv_dose_null.*, test_key.*, sq_result_id.nextval, sysdate
679from hv_dose_null, test_key;
680commit;
681
682
683insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
684with test_key as (
685  select test_domain,
686  'hv_du_mismatch' test_name from etl_test_domain_meds
687  ),
688mismatch as (
689  select count(*) test_value
690  from clarity.order_med
691  where dose_unit_c != hv_dose_unit_c
692  and dose_unit_c is not null and hv_dose_unit_c is not null
693  )
694select mismatch.*, test_key.*, sq_result_id.nextval, sysdate
695from mismatch, test_key;
696commit;
697
698
699insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
700with test_key as (
701  select test_domain,
702  'unknown_dose_values' test_name from etl_test_domain_meds
703  ),
704non_null_orders as(
705  select hv_discrete_dose from clarity.order_med where hv_discrete_dose is not null
706  ),
707numeric_sub as(
708  select regexp_substr( nno.hv_discrete_dose, '(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?' ) ss, nno.hv_discrete_dose s
709  from non_null_orders nno
710  ),
711not_numeric as(
712  select ns.s
713  from numeric_sub ns
714  where ns.ss is null or length(ns.s) != length(ns.ss)
715  ),
716unknown as (
717  select count(*) test_value
718  from not_numeric nn
719  where not regexp_like( nn.s, '(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?\-(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?' )
720  )
721select unknown.*, test_key.*, sq_result_id.nextval, sysdate
722from unknown, test_key
723;
724
725
726/* Counting inpatient versus outpatient
727with outp as(
728  select count(*) cnt
729  from clarity.order_med com
730  join clarity.ip_frequency freq on freq.freq_id=com.hv_discr_freq_id
731  where com.ordering_mode = 'Outpatient' or com.ordering_mode_c = 1
732  ),
733inp as(
734  select count(*) cnt
735  from clarity.order_med com
736  join clarity.ip_frequency freq on freq.freq_id=com.hv_discr_freq_id
737  where com.ordering_mode = 'Inpatient' or com.ordering_mode_c = 2
738  ),
739tot as(
740  select count(*) cnt
741  from clarity.order_med com
742  join clarity.ip_frequency freq on freq.freq_id=com.hv_discr_freq_id
743  )
744select
745  tot.cnt total,
746  outp.cnt outpatient, round(100*(outp.cnt/tot.cnt), 2) outp_percent,
747  inp.cnt inpatient, round(100*(inp.cnt/tot.cnt), 2) inp_percent
748from
749  outp, inp, tot;
750*/
751
752--TODO: consider order_status?  We have things like discontinued, dispensed, etc
753/* 
754Note: The clarity.zc_freq_type has no rows.  From the "Clarity Data Dictionary":
7551-Specified   
7562-Non-Specified   
7575-When   
7586-Continuous   
759
760TODO: Consider "Continuous"  We said to skip infusions for now (#1039)- is
761"Continuous" the right way to identify infusions?
762
763
764"Clarity Data Dictionary" ip_frequency:
765NUMBER_OF_TIMES EFQ
766This determines how often a task is to be scheduled (the meaning varies
767depending on if the type is frequency or period). Only integers are allowed as
768we move forward, but historical frequency records could contain decimals.   
769
770Argh..."meaning varies"...
771THREE TIMES DAILY PRN = 3 BUT, EVERY  6 HOURS PRN = 6.
772
773FREQ_PERIOD:
7741-Frequency   
7752-Period   
776*/
777
778/* Eyeballing facts by time code.
779with fact_counts_by_code as(
780  select count(*) cnt, freq.time_unit
781  from clarity.order_med com
782  left join clarity.ip_frequency freq on freq.freq_id=com.hv_discr_freq_id
783  group by freq.time_unit
784  order by count(*) desc
785  )
786select cbc.cnt, cbc.time_unit--zctm.name
787from fact_counts_by_code cbc
788--left join clarity.ZC_NEAREST_MED_TIM zctm on zctm.nearest_med_tim_c = cbc.time_unit;
789*/
790
791
792/* Clarity Data Dictionary: ip_frequency
793End Duration is used to determine the default end date and end time of an
794order placed with a When type frequency. It is the number of hours the order
795should remain on the MAR before being marked as inactive.
796
797Double-check to be sure that the end_duration is always <= 24 for "once" types.
798*/
799insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
800with test_key as (
801  select test_domain,
802  'once_is_lte_24' test_name from etl_test_domain_meds
803  ),
804once_not_end_24hrs as (
805  select count(*) test_value
806  from clarity.order_med com
807  left join clarity.ip_frequency freq on freq.freq_id=com.hv_discr_freq_id
808  where com.hv_discr_freq_id is not null and freq.freq_id is not null
809    and freq.freq_type = 5
810    and freq.end_duration > 24
811  )
812select once_not_end_24hrs.*, test_key.*, sq_result_id.nextval, sysdate
813from once_not_end_24hrs, test_key;
814commit;
815
816
817/* Multiple frequencies with the same name.
81817      TWICE DAILY
81914      DAILY
8205       THREE TIMES DAILY
8215       ONCE
822....etc...85 rows total
823*/
824/*
825select count(*), freq.freq_name
826from clarity.ip_frequency freq
827group by freq.freq_name
828having count(*) > 1
829order by count(*) desc;
830*/
831
832
833/* I don't see the number_of_times field being populated for most of the orders.
834Pick a few that have the most orders.  I'm using the "name" field rather than
835the code because I found that there are multiple codes that point to the same
836name in some cases.
837*/
838create or replace view freq_multiplier as(
839  select 'DAILY' freq_name, 1 multiplier from dual union all
840  select 'DAILY WITH BREAKFAST' freq_name, 1 multiplier from dual union all
841  select 'EVERY 24 HOURS' freq_name, 1 multiplier from dual union all
842  select 'AT BEDTIME DAILY' freq_name, 1 multiplier from dual union all
843  select 'TWICE DAILY' freq_name, 2 multiplier from dual union all
844  select 'TWICE DAILY BEFORE MEALS' freq_name, 2 multiplier from dual union all
845  select 'TWICE DAILY AFTER MEALS' freq_name, 2 multiplier from dual union all
846  select 'EVERY  8 HOURS' freq_name, 3 multiplier from dual union all
847  select 'THREE TIMES DAILY' freq_name, 3 multiplier from dual union all
848  select 'THREE TIMES DAILY WITH MEALS' freq_name, 3 multiplier from dual union all
849  select 'EVERY  6 HOURS' freq_name, 4 multiplier from dual union all
850  select 'FOUR TIMES DAILY' freq_name, 4 multiplier from dual union all
851  select 'EVERY  4 HOURS' freq_name, 6 multiplier from dual
852  );
853
854
855/* Make a view for dose information
856*/
857create or replace view dose_info as select * from(
858with using_num_times as(
859  select com.order_med_id, freq.freq_period, freq.freq_name,
860  freq.number_of_times, freq.time_unit, freq.prn_yn, com.hv_discrete_dose,
861  cmu.name hv_units, com.description, freq.freq_type,
862 
863  case
864    /* If the frequency type is "once" (5), then the daily dose is the
865    hv_discrete_dose.  Note that in the where clause we make sure
866    hv_discrete_dose is not null.  Also note that "to_number" was used here -
867    it appear's it's not needed below where we multiply with a number.
868    */
869    when freq.freq_type = 5 then to_number(com.hv_discrete_dose)
870    else
871      case --time units
872        when freq.time_unit = 1 then null --minutes
873        when freq.time_unit = 2 then --hours
874          case
875            when freq.freq_period = 2 then --hours between
876              com.hv_discrete_dose * ( 24 / freq.number_of_times )
877            when freq.freq_period = 1 then --times per day
878              com.hv_discrete_dose * freq.number_of_times
879          end
880        when freq.time_unit = 3 then null --days
881        when freq.time_unit = 4 then null --weeks
882        when freq.time_unit = 5 then null --months
883        else null --unknown
884      end
885  end daily_dose
886
887  from clarity.order_med com
888  left join clarity.ip_frequency freq
889  on freq.freq_id=com.hv_discr_freq_id
890  left join clarity.zc_med_unit cmu
891  on cmu.disp_qtyunit_c = com.hv_dose_unit_c
892  left join clarity.zc_order_status os
893  on os.order_status_c = com.order_status_c
894  where ( com.ordering_mode = 'Inpatient' or com.ordering_mode_c = 2
895       or com.ordering_mode = 'Outpatient' or com.ordering_mode_c = 1 )
896  and com.hv_discrete_dose is not null
897  -- TODO: Removed dose ranges for now - consider bringing them back?
898  and not regexp_like( com.hv_discrete_dose, '(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?\-(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?' )
899  -- Remove PRN (#1039)
900  and freq.prn_yn != 'Y'
901  -- TODO: Handle 6 = "continuous" later - these are infusions?
902  and freq.freq_type != 6
903  )
904select unt.*,
905  -- if we didn't get the daily dose figured, do so here (reduce case nesting)
906  case
907    when unt.daily_dose is null then unt.hv_discrete_dose * fm.multiplier
908  end daily_dose_2
909from using_num_times unt
910left join freq_multiplier fm
911on fm.freq_name = unt.freq_name
912);
913
914
915insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
916with test_key as (
917  select test_domain,
918  'lte_one_dose_info_per_order' test_name from etl_test_domain_meds
919  ),
920once_freq_per_order as (
921  select count(*) test_value from (
922    select order_med_id from dose_info group by order_med_id having count(*) > 1 
923    )
924  )
925select once_freq_per_order.*, test_key.*, sq_result_id.nextval, sysdate
926from once_freq_per_order, test_key;
927commit;
928
929/* Overall unit breakdown
930
931with unit_breakdown as(
932  select count(*) cnt, cmu.name
933  from clarity.order_med com
934  join clarity.zc_med_unit cmu on cmu.disp_qtyunit_c = com.hv_dose_unit_c
935  group by cmu.name
936  order by count(*) desc
937  ),
938total_orders as(
939  select count(*) cnt from clarity.order_med
940  ),
941report as(
942  select unit_breakdown.cnt, round(100*(unit_breakdown.cnt/total_orders.cnt), 2) prcnt,
943  unit_breakdown.name
944  from unit_breakdown, total_orders
945  )
946select * from report
947order by prcnt desc;
948*/
949
950
951/* how many orders? */
952--select count(*) from clarity.order_med;
953
954/* how many are ranges?
955with tot as(
956  select count(*) cnt
957  from dose_info di
958  ),
959rng as(
960  select count(*) cnt
961  from dose_info di
962  where regexp_like( di.hv_discrete_dose, '(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?\-(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?' )
963  )
964select tot.cnt, rng.cnt, (rng.cnt / tot.cnt) prcnt_are_range
965from rng, tot;
966*/
967
968/* Exploring things that have ranges - any patterns?  hmm...not as much as I'd hoped.
969select * from dose_info di
970where regexp_like( di.hv_discrete_dose, '(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?\-(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?' )
971and (di.prn_yn is null or di.prn_yn = 'N')
972and di.freq_id not in(
973  200592, --SEE ADMIN INSTRUCTIONS
974  200962, --** TITRATE AS DIRECTED **
975  200560, --** TITRATE AS DIRECTED (DAILY)
976  200902, --ONCE   <--- HANDLE SEPARATELY
977  200964, --ONCE IN OR  <--- HANDLE SEPARATELY
978  200905) --CONTINUOUS
979;
980*/
981
982
983/*
984Daily dose:  Amount provided in 24-hour period (#1039)
985
986As per:
987https://community.i2b2.org/wiki/display/DevForum/Metadata+XML+for+Medication+Modifiers
988
989Quantity is part of the value specification, and has units that are in the
990denominator of the unit's string.  If the value of quantity is null, then the
991value is assumed to be 1.
992*/
993
994
995create or replace view kuh_daily_dose_med_mods as (
996select distinct
997  medo.encounter_ide, medo.part,
998  medo.patient_ide, medo.concept_cd, medo.provider_id, medo.start_date, 
999  case
1000    when lower(di.hv_units) in ('mg', 'g', 'mcg') then 'MedObs:Dose|mg'
1001    when lower(di.hv_units) in ('tab') then 'MedObs:Dose|tab'
1002    when lower(di.hv_units) in ('l', 'ml') then 'MedObs:Dose|l'
1003    when lower(di.hv_units) in ('units') then 'MedObs:Dose|units'
1004    when lower(di.hv_units) in ('meq') then 'MedObs:Dose|meq'
1005    when lower(di.hv_units) in ('cap') then 'MedObs:Dose|cap'
1006    when lower(di.hv_units) in ('puff') then 'MedObs:Dose|puff'
1007    when lower(di.hv_units) in ('drop') then 'MedObs:Dose|drop'
1008    else to_char(1/0) --only known units in where clause
1009  end modifier_cd,
1010  medo.instance_num, 'N' as valtype_cd, 'E' as tval_char,
1011 
1012  case
1013    when di.daily_dose is not null then to_number(di.daily_dose)
1014    when di.daily_dose_2 is not null then to_number(di.daily_dose_2)
1015    else 1/0 --where clause should enfroce anyway
1016  end nval_num,
1017  '@' as valueflag_cd,
1018  /* https://community.i2b2.org/wiki/display/DevForum/Metadata+XML+for+Medication+Modifiers
1019  UnitValues is the parent tag of a set of possibly repeating tags.  It contains
1020  data when the datatype is PosInteger, Integer, Float, and PosFloat.  All units
1021  are always LOWER CASE.
1022  */
1023  lower(di.hv_units) units_cd,
1024  -- If frequency is of type ONCE (5), then the end date is the start date
1025  case
1026    when di.freq_type = 5 then medo.start_date
1027    else medo.end_date
1028  end end_date,
1029  medo.location_cd,
1030  medo.confidence_num, medo.update_date, medo.order_med_id
1031from kuh_med_orders medo
1032join dose_info di on di.order_med_id = medo.order_med_id
1033where (di.daily_dose is not null or di.daily_dose_2 is not null)
1034  and lower(di.hv_units) in ('mg', 'g', 'mcg', 'tab', 'l', 'ml',
1035                             'units', 'meq', 'cap', 'puff', 'drop')
1036/* Note:  Dual maintenance with the concept load (c_metadataxml for dose
1037modifiers).  We can "exclude" all the units we don't support in the XML, but I
1038think it's easier to just avoid loading the facts.
1039*/
1040);
1041
1042
1043/* What's our coverage like
1044total facts, dose facts, percent, what % of possible covereage do we have based
1045on ip_freq links?
1046
1047with total_orders as(
1048  select count(*) cnt from kuh_med_orders
1049  ),
1050orders_with_dose_or_prn as(
1051  select count(*) cnt from(
1052    select instance_num from kuh_daily_dose_med_mods
1053    union
1054    select instance_num from kuh_prn_med_mods
1055    )
1056  )
1057select t.cnt total_facts, d.cnt dose_facts, 100 * (d.cnt/t.cnt) prcnt
1058from total_orders t, orders_with_dose_or_prn d;
1059*/
1060
1061/* Another look at coverage (#1474).  Tables since views are so slow.
1062*/
1063
1064/*
1065create table kuh_med_orders_table as
1066  select
1067    order_med_id
1068  from
1069    kuh_med_orders
1070;
1071
1072
1073create table kuh_daily_dose_med_mods_table as
1074  select
1075    order_med_id
1076  from
1077    kuh_daily_dose_med_mods
1078;
1079
1080create table kuh_prn_med_mods_table as
1081  select
1082    order_med_id
1083  from
1084    kuh_prn_med_mods
1085;
1086
1087-- Make sure our counts match distinct
1088with total as(
1089  select count(*) cnt from kuh_med_orders_table
1090  ),
1091total_distinct as(
1092    select count(*) cnt from (select distinct order_med_id from kuh_med_orders_table)
1093  ) 
1094select case when total.cnt = total_distinct.cnt then 1 else 1/0 end counts_match
1095from total, total_distinct;
1096
1097with total as(
1098  select count(*) cnt from kuh_daily_dose_med_mods_table
1099  ),
1100total_distinct as(
1101  select count(*) cnt from (select distinct order_med_id from kuh_daily_dose_med_mods_table)
1102  ) 
1103select case when total.cnt = total_distinct.cnt then 1 else 1/0 end counts_match
1104from total, total_distinct;
1105
1106
1107with missing_dose as(
1108  select count(*) cnt from(
1109    select om.order_med_id
1110    from kuh_med_orders_table om
1111    left join kuh_daily_dose_med_mods_table dd on dd.order_med_id = om.order_med_id
1112    where dd.order_med_id is null
1113    )
1114  ),
1115with missing_prn_and_dose as(
1116  select count(*) cnt from(
1117    select om.order_med_id
1118    from kuh_med_orders_table om
1119    left join kuh_prn_med_mods_table prn on om.order_med_id = prn.order_med_id
1120    left join kuh_daily_dose_med_mods_table dd on dd.order_med_id = om.order_med_id
1121    where prn.order_med_id is null and dd.order_med_id is null
1122    )
1123  ),
1124total_orders as(
1125  select count(*) cnt from kuh_med_orders_table
1126  )
1127select
1128  round( (md.cnt / tord.cnt) * 100, 2) prcnt_missing_dose
1129  round( (mdprn.cnt / tord.cnt) * 100, 2) prcnt_missing_dose_and_prn
1130from missing_dose md, total_orders tord, missing_prn_and_dose mdprn;
1131
1132With addition of other units (#1474), we gained another 5% coverage.
1133*/
1134
1135/* For more coverage, what should we aim for next?  Exploration below
1136
1137select count(*) from kuh_med_orders;
1138select count(*) from kuh_prn_med_mods;
1139select count(*) from kuh_daily_dose_med_mods;
1140
1141drop table orders_with_no_dose;
1142create table orders_with_no_dose as(
1143  select distinct kuo.order_med_id
1144  from kuh_med_orders kuo
1145  left join kuh_daily_dose_med_mods dd on dd.order_med_id = kuo.order_med_id
1146  where dd.order_med_id is null
1147  );
1148select count(*) from orders_with_no_dose; --(67% uncovered - 33% covered (PRNs not removed))
1149
1150
1151drop table possible_dose_orders;
1152create table possible_dose_orders as(
1153  select distinct nd.order_med_id
1154  --from clarity.order_med com
1155  from orders_with_no_dose nd
1156  join clarity.order_med com on nd.order_med_id = com.order_med_id
1157  join clarity.ip_frequency freq on freq.freq_id=com.hv_discr_freq_id --we need to have ip_frequency
1158  join clarity.zc_med_unit munit on munit.disp_qtyunit_c = com.hv_dose_unit_c --need to have units
1159  where
1160    com.hv_discrete_dose is not null --discrete dose can't be null
1161  and
1162    (
1163    freq.freq_type = 5 --need to have ONCE...or
1164    or (freq.time_unit is not null and freq.freq_period is not null) --need to have a time unit/period otherwise...
1165    or freq.freq_name in (select name from freq_multiplier) --or, it's in the multiplier table by name
1166    ) 
1167  -- not a range
1168  and not regexp_like( com.hv_discrete_dose, '(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?\-(([0-9]+)|(\.[0-9]+))(\.[0-9]+)?' )
1169  -- PRN not null
1170  and freq.prn_yn is not null
1171  -- Avoid continuous
1172  and freq.freq_type != 6
1173);
1174
1175select count(*) from(
1176  select distinct pdo.order_med_id
1177  from possible_dose_orders pdo
1178  join clarity.order_med com on com.order_med_id = pdo.order_med_id
1179  join clarity.ip_frequency freq on freq.freq_id= com.hv_discr_freq_id
1180  where freq.prn_yn = 'N' --PRNs are already covered with blanket PRN modifier
1181  ); --4.6%
1182
1183--How about if we handled outpatient?
1184select count(*) from(
1185  select distinct pdo.order_med_id
1186  from possible_dose_orders pdo
1187  join clarity.order_med com on com.order_med_id = pdo.order_med_id
1188  join clarity.ip_frequency freq on freq.freq_id= com.hv_discr_freq_id
1189  where (com.ordering_mode = 'Outpatient' or com.ordering_mode_c = 1)
1190  and freq.prn_yn = 'N'
1191  ); -- <1%
1192
1193--What about going for days time unit?
1194select count(*) from(
1195  select distinct pdo.order_med_id
1196  from possible_dose_orders pdo
1197  join clarity.order_med com on com.order_med_id = pdo.order_med_id
1198  join clarity.ip_frequency freq on freq.freq_id= com.hv_discr_freq_id
1199  where (com.ordering_mode = 'Inpatient' or com.ordering_mode_c = 2
1200      or com.ordering_mode = 'Outpatient' or com.ordering_mode_c = 1)
1201  and freq.prn_yn = 'N'
1202  and freq.time_unit = 3
1203  );-- <1%
1204
1205drop table possible_dose_orders;
1206drop table orders_with_no_dose;
1207*/
1208
1209
1210/* Daily dose based on medication administration records (MAR) - see ticket 1552
1211Note that for now this code just handles "given" MAR records (mar_action_c = 1)
1212
1213The units are not the same for all MAR records of a given order id. For example,
1214an infusion may have a rate of mL/kg/hr, but bolus may be given just before the
1215infusion (units of mL).
1216*/
1217
1218
1219insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
1220with test_key as (
1221  select test_domain,
1222  'mar_sig_numeric' test_name from etl_test_domain_meds
1223  ),
1224not_numeric as (
1225  select count(*) test_result from (
1226    select trim(translate(sig, '0123456789.', ' ')) numisnull
1227    from clarity.mar_admin_info
1228    where sig is not null
1229    )
1230  where numisnull is not null
1231  )
1232select not_numeric.*, test_key.*, sq_result_id.nextval, sysdate
1233from not_numeric, test_key;
1234commit;
1235
1236
1237create or replace view kuh_mar_dose_med_mods as (
1238select distinct
1239  medo.encounter_ide, medo.part,
1240  medo.patient_ide, medo.concept_cd, medo.provider_id,
1241  mar.taken_time start_date, 
1242  case
1243    when lower(mu.name) in ('mg', 'g', 'mcg') then 'MedObs:MAR_Dose|mg'
1244    when lower(mu.name) in ('tab') then 'MedObs:MAR_Dose|tab'
1245    when lower(mu.name) in ('l', 'ml') then 'MedObs:MAR_Dose|l'
1246    when lower(mu.name) in ('units') then 'MedObs:MAR_Dose|units'
1247    when lower(mu.name) in ('meq') then 'MedObs:MAR_Dose|meq'
1248    when lower(mu.name) in ('cap') then 'MedObs:MAR_Dose|cap'
1249    when lower(mu.name) in ('puff') then 'MedObs:MAR_Dose|puff'
1250    when lower(mu.name) in ('drop') then 'MedObs:MAR_Dose|drop'
1251    else to_char(1/0) --only known units in where clause
1252  end modifier_cd,
1253  inst.instance_num, 'N' as valtype_cd, 'E' as tval_char,
1254  sig nval_num, --test above verifies we can convert to a number
1255  '@' as valueflag_cd,
1256  /* https://community.i2b2.org/wiki/display/DevForum/Metadata+XML+for+Medication+Modifiers
1257  UnitValues is the parent tag of a set of possibly repeating tags.  It contains
1258  data when the datatype is PosInteger, Integer, Float, and PosFloat.  All units
1259  are always LOWER CASE.
1260  */
1261  lower(mu.name) units_cd,
1262  mar.taken_time end_date,
1263  medo.location_cd,
1264  medo.confidence_num, medo.update_date, medo.order_med_id
1265from kuh_med_orders medo
1266join clarity.mar_admin_info mar on mar.order_med_id = medo.order_med_id
1267join clarity.zc_med_unit mu on mu.disp_qtyunit_c = mar.dose_unit_c
1268join mar_admin_instance inst on inst.order_med_id = mar.order_med_id
1269  and inst.line = mar.line
1270where mar.taken_time is not null and mar.sig is not null and
1271lower(mu.name) in ('mg', 'g', 'mcg', 'tab', 'l', 'ml',
1272                   'units', 'meq', 'cap', 'puff', 'drop')
1273  and trim(translate(mar.sig, '0123456789.', ' ')) is null
1274/* Note:  Dual maintenance with the concept load (c_metadataxml for dose
1275modifiers).  We can "exclude" all the units we don't support in the XML, but I
1276think it's easier to just avoid loading the facts.
1277*/
1278);
Note: See TracBrowser for help on using the repository browser.