CREATE OR REPLACE PACKAGE daily_dose_pkg AUTHID CURRENT_USER
IS
/* Create Daily Dose Era Table
Logic copied from Patick Ryans SQL Server script
CDMv4_build_dose_eras_18jul2013.sql
PreConditions
1) drug_dose table that maps Drug_concept_id to
ingredient and dose value
2) empty dose_era table and global temp table dose_era_1
on commit preserve rows, see dose_era.ddl
( dose_era_id INTEGER NOT NULL
, person_id INTEGER NOT NULL
, drug_concept_id INTEGER NOT NULL
, daily_dose NUMBER NOT NULL
, dose_unit VARCHAR2( 60 ) NOT NULL
, dose_era_start_date DATE NOT NULL
, dose_era_end_date DATE NOT NULL
, drug_exposure_count NUMBER( 3 ) NOT NULL
);
3) The drug_exposure, dose_era and drug_dose
tables ( or view or synonyms ) all exist
in the schema running this procedure
PostCondition
1) dose_era table is filled
*/
PROCEDURE version;
PROCEDURE create_era;
PROCEDURE create_era( pass IN INTEGER );
END daily_dose_pkg;
/
CREATE OR REPLACE PACKAGE BODY daily_dose_pkg
IS
-- global constant
version_ CONSTANT varchar2( 8 ) := 1.0;
newLine CONSTANT char(1) := chr( 10 ) ;
PROCEDURE get_exposure_cursor( pass IN integer
, drug_exposure_cur OUT sys_refCursor
)
IS
BEGIN
IF pass = 1
THEN
OPEN drug_exposure_cur
FOR
SELECT DISTINCT
de1.PERSON_ID
, dd1.ingredient_id as drug_concept_id
, ( 1.0*de1.quantity / de1.days_supply ) * dd1.dose_value AS daily_dose
, dd1.dose_unit
, de1.DRUG_EXPOSURE_START_DATE
, CASE WHEN de1.drug_exposure_end_date IS NULL
THEN CASE WHEN de1.days_supply > 0 and de1.days_supply < 365
THEN de1.DRUG_EXPOSURE_START_DATE + de1.days_supply
ELSE de1.DRUG_EXPOSURE_START_DATE
END
ELSE de1.drug_exposure_end_date
END AS drug_exposure_end_date
, 1 AS drug_exposure_count
FROM DRUG_EXPOSURE de1
INNER JOIN -- get ingredient(s) and drug doses for drug_id
( SELECT drug_id, ingredient_id, dose_value, dose_unit
FROM drug_dose
WHERE dose_unit IS NOT NULL
) dd1 ON de1.DRUG_CONCEPT_ID = dd1.drug_id
WHERE de1.days_supply > 0 and de1.days_supply < 365
AND de1.quantity > 0 and de1.quantity < 1100
-- AND de1.person_id = 1073900354 and dd1.ingredient_id = 1545958 --test case
ORDER BY de1.PERSON_ID, dd1.ingredient_id, de1.drug_exposure_start_date
, dd1.dose_unit, daily_dose;
ELSE -- pass 2
OPEN drug_exposure_cur
FOR
SELECT PERSON_ID
, drug_concept_id
, daily_dose
, dose_unit
, dose_era_start_date
, dose_era_end_date
, drug_exposure_count
FROM dose_era_1
ORDER BY PERSON_ID, drug_concept_id, dose_era_start_date
, dose_unit, daily_dose;
END IF; -- pass
END get_exposure_cursor;
PROCEDURE insert_dose( pass IN INTEGER
, id IN dose_era.dose_era_id%TYPE
, person_id IN dose_era.person_id%TYPE
, drug_concept IN dose_era.drug_concept_id%TYPE
, dose IN dose_era.daily_dose%TYPE
, unit IN dose_era.dose_unit%TYPE
, start_date IN date
, end_date IN date
, exposures IN dose_era.drug_exposure_count%TYPE
)
IS
BEGIN
IF pass = 1
THEN
INSERT INTO DOSE_ERA_1( dose_era_id, person_id, drug_concept_id, daily_dose
, dose_unit, dose_era_start_date, dose_era_end_date
, drug_exposure_count )
VALUES( id, person_id, drug_concept, dose, unit
, start_date, end_date, exposures );
ELSE -- pass 2
INSERT INTO DOSE_ERA( dose_era_id, person_id, drug_concept_id, daily_dose
, dose_unit, dose_era_start_date, dose_era_end_date
, drug_exposure_count )
VALUES( id, person_id, drug_concept, dose, unit
, start_date, end_date, exposures );
END IF; -- pass
END insert_dose;
-- exposed procedures
PROCEDURE version
IS
BEGIN
dbms_output.put_line( 'Version: ' || version_ );
END version;
PROCEDURE create_era
IS
BEGIN
create_era( pass=> 1 );
create_era( pass=> 2 );
END create_era;
PROCEDURE create_era( pass IN integer )
IS
drug_exposure_cur SYS_refCursor;
last_personid drug_exposure.person_id%TYPE;
last_drug_concept_id drug_exposure.drug_concept_id%TYPE;
last_daily_dose NUMBER;
last_dose_unit drug_dose.dose_unit%TYPE;
last_dtstart DATE;
last_dtend DATE;
last_exposure_count INTEGER;
current_personid drug_exposure.person_id%TYPE;
current_dtstart DATE;
current_dtend DATE;
current_drug_concept_id drug_exposure.drug_concept_id%TYPE;
current_daily_dose NUMBER;
current_dose_unit drug_dose.dose_unit%TYPE;
current_exposure_count INTEGER;
drug_count INTEGER;
dose_era_id INTEGER;
BEGIN
get_exposure_cursor( pass=> pass, drug_exposure_cur=> drug_exposure_cur );
FETCH drug_exposure_cur
INTO last_personid, last_drug_concept_id, last_daily_dose
, last_dose_unit, last_dtstart, last_dtend
, last_exposure_count;
drug_count := last_exposure_count;
dose_era_id := 1;
LOOP
FETCH drug_exposure_cur
INTO current_personid, current_drug_concept_id, current_daily_dose
, current_dose_unit
, current_dtstart, current_dtend, current_exposure_count;
EXIT WHEN drug_exposure_cur%NOTFOUND;
/*************************************************/
IF current_personid = last_personid
AND current_drug_concept_id = last_drug_concept_id
AND current_dose_unit = last_dose_unit
THEN --2 --continuation of same dose era if daily dose within 10%
--, allow 10d of stockpiling and 30d persistence window
IF current_daily_dose >= 0.9 * last_daily_dose
AND current_daily_dose <= 1.1 * last_daily_dose
AND current_dtstart >= last_dtend - 10
AND current_dtstart <= last_dtend + 30
THEN --3
last_dtend := GREATEST( current_dtend, last_dtend );
last_exposure_count := last_exposure_count + current_exposure_count;
ELSE --3 -- dose stays same but large overlap
-- such that we really need to add the dose together
IF current_daily_dose >= 0.9 * last_daily_dose
AND current_daily_dose <= 1.1 * last_daily_dose
AND current_dtstart < last_dtend - 10
THEN --4
IF current_dtstart > last_dtstart
THEN --5
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> current_dtstart - 1
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
END IF; --5 current_dtstart > last_dtstart
--get up overlapping part
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_daily_dose := last_daily_dose + current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := LEAST( last_dtend, current_dtend );
last_exposure_count := last_exposure_count + current_exposure_count;
IF last_dtEnd != current_dtEnd
THEN
-- Have to save the end part of record that does not overlap
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> current_personid
, drug_concept=> current_drug_concept_id
, dose=> current_daily_dose
, unit=> current_dose_unit
, start_date=> LEAST( last_dtEnd, current_dtEnd ) + 1
, end_date=> GREATEST( last_dtEnd, current_dtEnd )
, exposures=> 1 ) ;
END IF; -- tail end of overlapping eras
ELSE--4
--if dailydose changes within 30d persistence window
IF( current_daily_dose < 0.9 * last_daily_dose
OR current_daily_dose > 1.1 * last_daily_dose
)
AND current_dtstart <= last_dtend + 30
THEN --5 --no overlap from prior record
IF current_dtstart >= last_dtend
THEN --6
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_daily_dose := current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := current_dtend;
last_exposure_count := current_exposure_count;
ELSE --6 --there is overlap, so need to combine dailydose as long as these overlap
--write row for part prior to overlap
IF current_dtstart > last_dtstart
THEN --7
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> current_dtstart - 1
, exposures=> current_exposure_count );
dose_era_id := dose_era_id + 1;
END IF; -- 7
--get up overlapping part
last_daily_dose := last_daily_dose + current_daily_dose;
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := LEAST( last_dtend, current_dtend );
last_exposure_count := last_exposure_count + current_exposure_count;
IF last_dtEnd != current_dtEnd
THEN
-- Have to save the end part of record that does not overlap
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> current_personid
, drug_concept=> current_drug_concept_id
, dose=> current_daily_dose
, unit=> current_dose_unit
, start_date=> LEAST( last_dtEnd, current_dtEnd ) + 1
, end_date=> GREATEST( last_dtEnd, current_dtEnd )
, exposures=> 1 ) ;
END IF; -- tail end of overlapping eras
END IF; --6 current_dtstart >= last_dtend
ELSE --5
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_daily_dose := current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := current_dtend;
last_exposure_count := current_exposure_count;
END IF; --5
END IF; --4
END IF; --3
ELSE --2
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
last_personid := current_personid;
last_drug_concept_id := current_drug_concept_id;
last_daily_dose := current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := current_dtend;
last_exposure_count := current_exposure_count;
END IF;
END LOOP;
CLOSE drug_exposure_cur;
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
COMMIT;
END create_era;
END daily_dose_pkg;
/
