arb.sql

WITH arb_drug AS (
  SELECT DISTINCT
    drug
    , CASE
      WHEN UPPER(drug) LIKE '%AZILSARTAN%' OR UPPER(drug) LIKE '%EDARBI%'
      OR UPPER(drug) LIKE '%CANDESARTAN%' OR UPPER(drug) LIKE '%ATACAND%'
      OR UPPER(drug) LIKE '%IRBESARTAN%' OR UPPER(drug) LIKE '%AVAPRO%'
      OR UPPER(drug) LIKE '%LOSARTAN%' OR UPPER(drug) LIKE '%COZAAR%'
      OR UPPER(drug) LIKE '%OLMESARTAN%' OR UPPER(drug) LIKE '%BENICAR%'
      OR UPPER(drug) LIKE '%TELMISARTAN%' OR UPPER(drug) LIKE '%MICARDIS%'
      OR UPPER(drug) LIKE '%VALSARTAN%' OR UPPER(drug) LIKE '%DIOVAN%'
      OR UPPER(drug) LIKE '%SACUBITRIL%' OR UPPER(drug) LIKE '%ENTRESTO%'
        THEN 1
      ELSE 0
      END AS arb
  FROM `physionet-data.mimiciv_hosp.prescriptions`
)

SELECT
  pr.subject_id
  , pr.hadm_id
  , pr.drug AS arb
  , pr.starttime
  , pr.stoptime
FROM
  `physionet-data.mimiciv_hosp.prescriptions` pr
  INNER JOIN arb_drug
  ON pr.drug = arb_drug.drug
WHERE
  arb_drug.arb = 1
;