title: Creating an index date to filter by the date of an event contained in two different tables
body: <p>I'm working with a medical claims (<code>dx_claim_onc</code>) and a pharmacy claims database (<code>rx_claim_onc</code>), and I want to only return patient IDs (<code>ptnt_id</code>) that meet the following criteria:
  a. 6 months of data prior to diagnosis
  b. At least 2 months in data post-diagnosis</p>
  <p>I'm pulling only patients that have at least two diagnoses of interest in <code>dx_claim_onc</code> , which I've done in a subquery (I've only included for reference/context). From this cohort, I only want to count patients that have at least 6 months of data prior to diagnosis AND at least 60 days in data post-diagnosis (this can be from either <code>dx_claim_onc</code> or <code>rx_claim_onc</code>). So for instance, if a patient has one of the diagnosis codes of interest on 07/01/20, they'll need to have a record in <code>dx_claim_onc</code>(which is indicated by <code>dt_of_svc</code>) or in <code>rx_claim_onc</code> (which is indicated by <code>DSPND_DT</code>) 6 months before their diagnosis (i.e. 01/01/20) AND 2 months after their diagnosis (i.e. 09/01/20).</p>
  <p>To do this, I tried to create an index date using the <code>dt_of_sv</code> column and then referencing this to determine whether those two conditions were met:</p>
  <pre><code>SELECT count(*)
  FROM
  (
      SELECT mc.ptnt_id, min(mc.dt_of_svc) as index_dt, min(mc.dt_of_svc) as dx_prior, max(mc.dt_of_svc) as dx_post
      FROM dx_claim_onc mc
      where mc.icd_cd in (# list of ICD codes)
      group by mc.ptnt_id
      having count(mc.dt_of_svc) &gt;= 2
  ) mc
  LEFT JOIN
  (
  SELECT ptnt_id, min(rx.DSPND_DT) as rx_prior, max(rx.DSPND_DT) as rx_post
  FROM rx_claim_onc rx
  group by rx.ptnt_id
  ) rx
  ON mc.ptnt_id = rx.ptnt_id
  AND (rx_prior &lt;= add_months(index_dt, -6) OR dx_post &gt;= add_months(index_dt, -6))
  AND (rx_post &gt;= add_months(index_dt, 2) OR dx_post &gt;= add_months(index_dt, 2))
  </code></pre>
  <p>The query itself runs, but returns 0 patients, which I know is not true. Also, <code>dt_of_svc</code> and <code>DSPND_DT</code> are both strings. Would I need to do something like to_date() to convert them from strings?</p>
  <p>Here is some sample data along with desired results:</p>
  <p><strong>dx_claim</strong></p>
  <pre><code>claim_key_dx | ptnt_id | icd_cd | dt_of_svc
  101             444       10      01012020
  102             444       55      10152020
  103             444       80      03012021
  104             999       55      06012020
  105            1555       80      03012021
  106            1555       55      03022021
  </code></pre>
  <p><strong>rx_claim</strong></p>
  <pre><code>claim_key_rx | ptnt_id | DSPND_DT
  -------------+---------+----------
  201             999      02102018
  202             999      03012021
  203            1555      12052019
  203            1555      05302020
  </code></pre>
  <p>assuming icd_cd = 55 is one icd codes of interest:</p>
  <p><strong>ptnt_id 444</strong>: 444 is diagnosed with icd_cd = 55 on 10152020, and they have at least 6 months in the data before the diagnosis (this is calculated by using 10152020 as the index date and then taking the difference between this and their oldest record, which would be 10152020 – 01012020) AND they have at least 2 months of data after the diagnosis (difference between most recent data and date of diagnosis, which in this case would be 03012021 – 10152020)</p>
  <p><strong>ptnt_id 999</strong>: INCLUDED into the cohort. Similar to above, just that we validate by looking at <code>rx_claim_onc</code> and <code>DSPND_DT</code></p>
  <p><strong>ptnt_id 1555</strong>: NOT INCLUDED since they don't have any data for at least 2 months after their diagnosis on 03012021.</p>