.Rhistory

MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS unbounded preceding) AS start_ordinal,
-- this pulls the current START down from the prior rows so that the NULLs
-- from the END DATES will contain a value we can compare with
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type) AS overall_ord
-- this re-numbers the inner UNION so all rows are numbered ordered by the event date
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_exposure_start_date) AS start_ordinal
FROM ctePreDrugTarget
UNION ALL
SELECT person_id, ingredient_concept_id, drug_exposure_end_date, 1 AS event_type, NULL
FROM ctePreDrugTarget
)")
cteSubExposureEndDates %>%
mutate(is_valid_end_date = (2 * start_ordinal) - overall_ord == 0)
cteSubExposureEndDates <- cteSubExposureEndDates %>%
filter(2*start_ordinal == overall_ord) %>%
rename(end_date = event_date)
cteDrugExposureEnds <- sqldf::sqldf("
SELECT
dt.person_id
, dt.ingredient_concept_id as drug_concept_id
, dt.drug_exposure_start_date
, MIN(e.end_date) AS drug_sub_exposure_end_date
FROM ctePreDrugTarget dt
JOIN cteSubExposureEndDates e
ON dt.person_id = e.person_id
AND dt.ingredient_concept_id = e.ingredient_concept_id
AND e.end_date >= dt.drug_exposure_start_date
GROUP BY
dt.drug_exposure_id
, dt.person_id
, dt.ingredient_concept_id
, dt.drug_exposure_start_date
")
cteDrugExposureEnds
cteSubExposures <- sqldf::sqldf("
SELECT
ROW_NUMBER() OVER (PARTITION BY person_id, drug_concept_id, drug_sub_exposure_end_date ORDER BY person_id) as row_number,
person_id,
drug_concept_id,
MIN(drug_exposure_start_date) AS drug_sub_exposure_start_date,
drug_sub_exposure_end_date,
COUNT(*) AS drug_exposure_count
FROM cteDrugExposureEnds
GROUP BY person_id, drug_concept_id, drug_sub_exposure_end_date")
cteSubExposures
cteFinalTarget <- sqldf::sqldf("
SELECT
row_number,
person_id,
drug_concept_id as ingredient_concept_id,
drug_sub_exposure_start_date,
drug_sub_exposure_end_date, drug_exposure_count,
drug_sub_exposure_end_date - drug_sub_exposure_start_date AS days_exposed
FROM cteSubExposures
")
cteFinalTarget
cteEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date, event_type,
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id ORDER BY event_date, event_type) AS overall_ord
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_sub_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_sub_exposure_start_date) AS start_ordinal
FROM cteFinalTarget
UNION ALL
-- pad the end dates by 30 to allow a grace period for overlapping ranges.
SELECT person_id, ingredient_concept_id, drug_sub_exposure_end_date + 30, 1 AS event_type, NULL
FROM cteFinalTarget
)")
cteEndDates
cteEndDates <- cteEndDates %>%
filter(2*start_ordinal == overall_ord) %>%
# unpad the end dates
mutate(event_date = event_date - 30) %>%
select(person_id, ingredient_concept_id, end_date = event_date)
cteDrugEraEnds <- sqldf::sqldf("
SELECT
ft.person_id
, ft.ingredient_concept_id as drug_concept_id
, ft.drug_sub_exposure_start_date
, MIN(e.end_date) AS drug_era_end_date
, drug_exposure_count
, days_exposed
FROM cteFinalTarget ft
JOIN cteEndDates e
ON ft.person_id = e.person_id
AND ft.ingredient_concept_id = e.ingredient_concept_id
AND e.end_date >= ft.drug_sub_exposure_start_date
GROUP BY
ft.person_id
, ft.ingredient_concept_id
, ft.drug_sub_exposure_start_date
, drug_exposure_count
, days_exposed")
cteDrugEraEnds
sqldf::sqldf(
"SELECT
row_number()over(order by person_id) drug_era_id
, person_id
, drug_concept_id
, MIN(drug_sub_exposure_start_date) AS drug_era_start_date
, drug_era_end_date
, SUM(drug_exposure_count) AS drug_exposure_count
, (drug_era_end_date - MIN(drug_sub_exposure_start_date)) - SUM(days_exposed) as gap_days
FROM cteDrugEraEnds dee
GROUP BY person_id, drug_concept_id, drug_era_end_date;")
concept %>% filter(CONCEPT_ID %in% c(1127433, 1127078))
concept %>% filter(CONCEPT_ID == 1125315)
knitr::opts_chunk$set(echo = TRUE, message = FALSE)
library(dplyr)
library(Eunomia)
cd <- getEunomiaConnectionDetails()
con <- DatabaseConnector::connect(cd)
concept <- dbGetQuery(con, "select * from concept") %>% tibble()
concept_ancestor <- dbGetQuery(con, "select * from concept_ancestor") %>% tibble()
DatabaseConnector::disconnect(con)
# concept_ancestor %>% filter(ANCESTOR_CONCEPT_ID == 1125315)
drug_exposure <- tribble(
~drug_exposure_id, ~person_id, ~drug_concept_id, ~drug_exposure_start_date, ~drug_exposure_end_date, ~days_supply,
1,                 1,          1127433,           1,                         50,                      NA_real_,
2,                 1,          1127078,          10,                         60,                      NA_real_,
3,                 1,          1127078,          80,                        120,                      NA_real_
)
drug_exposure
concept %>% filter(CONCEPT_ID %in% c(1127433, 1127078))
ctePreDrugTarget <- sqldf::sqldf("
SELECT
d.drug_exposure_id
, d.person_id
, c.concept_id AS ingredient_concept_id
, d.drug_exposure_start_date AS drug_exposure_start_date
, d.days_supply AS days_supply
, COALESCE(
---NULLIF returns NULL if both values are the same, otherwise it returns the first parameter
NULLIF(drug_exposure_end_date, NULL),
---If drug_exposure_end_date != NULL, return drug_exposure_end_date, otherwise go to next case
NULLIF(drug_exposure_start_date + days_supply, drug_exposure_start_date),
---If days_supply != NULL or 0, return drug_exposure_start_date + days_supply, otherwise go to next case
drug_exposure_start_date + 1
---Add 1 day to the drug_exposure_start_date since there is no end_date or INTERVAL for the days_supply
) AS drug_exposure_end_date
FROM drug_exposure d
JOIN concept_ancestor ca ON ca.descendant_concept_id = d.drug_concept_id
JOIN concept c ON ca.ancestor_concept_id = c.concept_id
WHERE c.vocabulary_id = 'RxNorm' ---8 selects RxNorm from the vocabulary_id
AND c.concept_class_id = 'Ingredient'
AND d.drug_concept_id != 0 ---Our unmapped drug_concept_id's are set to 0, so we don't want different drugs wrapped up in the same era
AND coalesce(d.days_supply,0) >= 0
")
ctePreDrugTarget %>% arrange(drug_exposure_id)
concept %>% filter(CONCEPT_ID == 1125315)
cteSubExposureEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date, event_type,
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS unbounded preceding) AS start_ordinal,
-- this pulls the current START down from the prior rows so that the NULLs
-- from the END DATES will contain a value we can compare with
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type) AS overall_ord
-- this re-numbers the inner UNION so all rows are numbered ordered by the event date
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_exposure_start_date) AS start_ordinal
FROM ctePreDrugTarget
UNION ALL
SELECT person_id, ingredient_concept_id, drug_exposure_end_date, 1 AS event_type, NULL
FROM ctePreDrugTarget
)")
cteSubExposureEndDates %>%
mutate(is_valid_end_date = (2 * start_ordinal) - overall_ord == 0)
cteSubExposureEndDates <- cteSubExposureEndDates %>%
filter(2*start_ordinal == overall_ord) %>%
rename(end_date = event_date)
cteDrugExposureEnds <- sqldf::sqldf("
SELECT
dt.person_id
, dt.ingredient_concept_id as drug_concept_id
, dt.drug_exposure_start_date
, MIN(e.end_date) AS drug_sub_exposure_end_date
FROM ctePreDrugTarget dt
JOIN cteSubExposureEndDates e
ON dt.person_id = e.person_id
AND dt.ingredient_concept_id = e.ingredient_concept_id
AND e.end_date >= dt.drug_exposure_start_date
GROUP BY
dt.drug_exposure_id
, dt.person_id
, dt.ingredient_concept_id
, dt.drug_exposure_start_date
")
cteDrugExposureEnds
cteSubExposures <- sqldf::sqldf("
SELECT
ROW_NUMBER() OVER (PARTITION BY person_id, drug_concept_id, drug_sub_exposure_end_date ORDER BY person_id) as row_number,
person_id,
drug_concept_id,
MIN(drug_exposure_start_date) AS drug_sub_exposure_start_date,
drug_sub_exposure_end_date,
COUNT(*) AS drug_exposure_count
FROM cteDrugExposureEnds
GROUP BY person_id, drug_concept_id, drug_sub_exposure_end_date")
cteSubExposures
cteFinalTarget <- sqldf::sqldf("
SELECT
row_number,
person_id,
drug_concept_id as ingredient_concept_id,
drug_sub_exposure_start_date,
drug_sub_exposure_end_date, drug_exposure_count,
drug_sub_exposure_end_date - drug_sub_exposure_start_date AS days_exposed
FROM cteSubExposures
")
cteFinalTarget
cteEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date, event_type,
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id ORDER BY event_date, event_type) AS overall_ord
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_sub_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_sub_exposure_start_date) AS start_ordinal
FROM cteFinalTarget
UNION ALL
-- pad the end dates by 30 to allow a grace period for overlapping ranges.
SELECT person_id, ingredient_concept_id, drug_sub_exposure_end_date + 30, 1 AS event_type, NULL
FROM cteFinalTarget
)")
cteEndDates
cteEndDates <- cteEndDates %>%
filter(2*start_ordinal == overall_ord) %>%
# unpad the end dates
mutate(event_date = event_date - 30) %>%
select(person_id, ingredient_concept_id, end_date = event_date)
cteDrugEraEnds <- sqldf::sqldf("
SELECT
ft.person_id
, ft.ingredient_concept_id as drug_concept_id
, ft.drug_sub_exposure_start_date
, MIN(e.end_date) AS drug_era_end_date
, drug_exposure_count
, days_exposed
FROM cteFinalTarget ft
JOIN cteEndDates e
ON ft.person_id = e.person_id
AND ft.ingredient_concept_id = e.ingredient_concept_id
AND e.end_date >= ft.drug_sub_exposure_start_date
GROUP BY
ft.person_id
, ft.ingredient_concept_id
, ft.drug_sub_exposure_start_date
, drug_exposure_count
, days_exposed")
cteDrugEraEnds
sqldf::sqldf(
"SELECT
row_number()over(order by person_id) drug_era_id
, person_id
, drug_concept_id
, MIN(drug_sub_exposure_start_date) AS drug_era_start_date
, drug_era_end_date
, SUM(drug_exposure_count) AS drug_exposure_count
, (drug_era_end_date - MIN(drug_sub_exposure_start_date)) - SUM(days_exposed) as gap_days
FROM cteDrugEraEnds dee
GROUP BY person_id, drug_concept_id, drug_era_end_date;")
library(dplyr)
library(Eunomia)
cd <- getEunomiaConnectionDetails()
con <- DatabaseConnector::connect(cd)
concept <- dbGetQuery(con, "select * from concept") %>% tibble()
concept_ancestor <- dbGetQuery(con, "select * from concept_ancestor") %>% tibble()
DatabaseConnector::disconnect(con)
concept_ancestor %>% filter(ANCESTOR_CONCEPT_ID == 1125315)
con <- DatabaseConnector::connect(cd)
drug_exposure <- tribble(
~drug_exposure_id, ~person_id, ~drug_concept_id, ~drug_exposure_start_date, ~drug_exposure_end_date, ~days_supply,
1,                 1,          1127433,           1,                         50,                      NA_real_,
2,                 1,          1127078,          10,                         60,                      NA_real_,
3,                 1,          1127078,          80,                        120,                      NA_real_
)
drug_exposure
ctePreDrugTarget <- sqldf::sqldf("
SELECT
d.drug_exposure_id
, d.person_id
, c.concept_id AS ingredient_concept_id
, d.drug_exposure_start_date AS drug_exposure_start_date
, d.days_supply AS days_supply
, COALESCE(
---NULLIF returns NULL if both values are the same, otherwise it returns the first parameter
NULLIF(drug_exposure_end_date, NULL),
---If drug_exposure_end_date != NULL, return drug_exposure_end_date, otherwise go to next case
NULLIF(drug_exposure_start_date + days_supply, drug_exposure_start_date),
---If days_supply != NULL or 0, return drug_exposure_start_date + days_supply, otherwise go to next case
drug_exposure_start_date + 1
---Add 1 day to the drug_exposure_start_date since there is no end_date or INTERVAL for the days_supply
) AS drug_exposure_end_date
FROM drug_exposure d
JOIN concept_ancestor ca ON ca.descendant_concept_id = d.drug_concept_id
JOIN concept c ON ca.ancestor_concept_id = c.concept_id
WHERE c.vocabulary_id = 'RxNorm' ---8 selects RxNorm from the vocabulary_id
AND c.concept_class_id = 'Ingredient'
AND d.drug_concept_id != 0 ---Our unmapped drug_concept_id's are set to 0, so we don't want different drugs wrapped up in the same era
AND coalesce(d.days_supply,0) >= 0
")
ctePreDrugTarget %>% arrange(drug_exposure_id)
cteSubExposureEndDates <- cteSubExposureEndDates %>%
filter(2*start_ordinal == overall_ord) %>%
rename(end_date = event_date)
cteSubExposureEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date, event_type,
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS unbounded preceding) AS start_ordinal,
-- this pulls the current START down from the prior rows so that the NULLs
-- from the END DATES will contain a value we can compare with
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type) AS overall_ord
-- this re-numbers the inner UNION so all rows are numbered ordered by the event date
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_exposure_start_date) AS start_ordinal
FROM ctePreDrugTarget
UNION ALL
SELECT person_id, ingredient_concept_id, drug_exposure_end_date, 1 AS event_type, NULL
FROM ctePreDrugTarget
)")
cteSubExposureEndDates %>%
mutate(is_valid_end_date = (2 * start_ordinal) - overall_ord == 0)
cteSubExposureEndDates <- cteSubExposureEndDates %>%
filter(2*start_ordinal == overall_ord) %>%
rename(end_date = event_date)
cteSubExposureEndDates
cteDrugExposureEnds <- sqldf::sqldf("
SELECT
dt.person_id
, dt.ingredient_concept_id as drug_concept_id
, dt.drug_exposure_start_date
, MIN(e.end_date) AS drug_sub_exposure_end_date
FROM ctePreDrugTarget dt
JOIN cteSubExposureEndDates e
ON dt.person_id = e.person_id
AND dt.ingredient_concept_id = e.ingredient_concept_id
AND e.end_date >= dt.drug_exposure_start_date
GROUP BY
dt.drug_exposure_id
, dt.person_id
, dt.ingredient_concept_id
, dt.drug_exposure_start_date
")
cteDrugExposureEnds
cteSubExposures <- sqldf::sqldf("
SELECT
ROW_NUMBER() OVER (PARTITION BY person_id, drug_concept_id, drug_sub_exposure_end_date ORDER BY person_id) as row_number,
person_id,
drug_concept_id,
MIN(drug_exposure_start_date) AS drug_sub_exposure_start_date,
drug_sub_exposure_end_date,
COUNT(*) AS drug_exposure_count
FROM cteDrugExposureEnds
GROUP BY person_id, drug_concept_id, drug_sub_exposure_end_date")
cteSubExposures
cteFinalTarget <- sqldf::sqldf("
SELECT
row_number,
person_id,
drug_concept_id as ingredient_concept_id,
drug_sub_exposure_start_date,
drug_sub_exposure_end_date, drug_exposure_count,
drug_sub_exposure_end_date - drug_sub_exposure_start_date AS days_exposed
FROM cteSubExposures
")
cteFinalTarget
cteEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date, event_type,
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id ORDER BY event_date, event_type) AS overall_ord
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_sub_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_sub_exposure_start_date) AS start_ordinal
FROM cteFinalTarget
UNION ALL
-- pad the end dates by 30 to allow a grace period for overlapping ranges.
SELECT person_id, ingredient_concept_id, drug_sub_exposure_end_date + 30, 1 AS event_type, NULL
FROM cteFinalTarget
)")
cteEndDates
cteEndDates <- cteEndDates %>%
filter(2*start_ordinal == overall_ord) %>%
# unpad the end dates
mutate(event_date = event_date - 30) %>%
select(person_id, ingredient_concept_id, end_date = event_date)
cteDrugEraEnds <- sqldf::sqldf("
SELECT
ft.person_id
, ft.ingredient_concept_id as drug_concept_id
, ft.drug_sub_exposure_start_date
, MIN(e.end_date) AS drug_era_end_date
, drug_exposure_count
, days_exposed
FROM cteFinalTarget ft
JOIN cteEndDates e
ON ft.person_id = e.person_id
AND ft.ingredient_concept_id = e.ingredient_concept_id
AND e.end_date >= ft.drug_sub_exposure_start_date
GROUP BY
ft.person_id
, ft.ingredient_concept_id
, ft.drug_sub_exposure_start_date
, drug_exposure_count
, days_exposed")
cteDrugEraEnds
sqldf::sqldf(
"SELECT
row_number()over(order by person_id) drug_era_id
, person_id
, drug_concept_id
, MIN(drug_sub_exposure_start_date) AS drug_era_start_date
, drug_era_end_date
, SUM(drug_exposure_count) AS drug_exposure_count
, (drug_era_end_date - MIN(drug_sub_exposure_start_date)) - SUM(days_exposed) as gap_days
FROM cteDrugEraEnds dee
GROUP BY person_id, drug_concept_id, drug_era_end_date;")
con <- DatabaseConnector::connect(cd)
measurement <- dbGetQuery(con, "select * from measurement") %>% tibble()
View(measurement)
concept %>% filter(CONCEPT_ID %in% ctePreDrugTarget$ingredient_concept_id)
cteSubExposureEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date AS end_date
FROM cteSubExposureEndDates
WHERE (2 * e.start_ordinal) - e.overall_ord = 0")
cteSubExposureEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date, event_type,
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS unbounded preceding) AS start_ordinal,
-- this pulls the current START down from the prior rows so that the NULLs
-- from the END DATES will contain a value we can compare with
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type) AS overall_ord
-- this re-numbers the inner UNION so all rows are numbered ordered by the event date
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_exposure_start_date) AS start_ordinal
FROM ctePreDrugTarget
UNION ALL
SELECT person_id, ingredient_concept_id, drug_exposure_end_date, 1 AS event_type, NULL
FROM ctePreDrugTarget
)")
cteSubExposureEndDates %>%
mutate(is_valid_end_date = (2 * start_ordinal) - overall_ord == 0)
cteSubExposureEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date AS end_date
FROM cteSubExposureEndDates
WHERE (2 * e.start_ordinal) - e.overall_ord = 0")
cteSubExposureEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date AS end_date
FROM cteSubExposureEndDates
WHERE (2 * start_ordinal) - overall_ord = 0")
cteEndDates %>%
mutate(is_valid_end_date = (2 * start_ordinal) - overall_ord == 0)
cteEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date, event_type,
MAX(start_ordinal) OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id ORDER BY event_date, event_type) AS overall_ord
FROM (
-- select the start dates, assigning a row number to each
SELECT person_id, ingredient_concept_id, drug_sub_exposure_start_date AS event_date,
-1 AS event_type,
ROW_NUMBER() OVER (PARTITION BY person_id, ingredient_concept_id
ORDER BY drug_sub_exposure_start_date) AS start_ordinal
FROM cteFinalTarget
UNION ALL
-- pad the end dates by 30 to allow a grace period for overlapping ranges.
SELECT person_id, ingredient_concept_id, drug_sub_exposure_end_date + 30, 1 AS event_type, NULL
FROM cteFinalTarget
)")
cteEndDates %>%
mutate(is_valid_end_date = (2 * start_ordinal) - overall_ord == 0)
cteEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date - 30 AS end_date -- unpad the end date
FROM cteEndDates
WHERE (2 * e.start_ordinal) - e.overall_ord = 0")
cteEndDates <- sqldf::sqldf("
SELECT person_id, ingredient_concept_id, event_date - 30 AS end_date -- unpad the end date
FROM cteEndDates
WHERE (2 * start_ordinal) - overall_ord = 0")