title: How to left join on a dataframe based on dates on `dbplyr`
body: <p>I am trying to join two <code>data.frame</code>s based on a condition. Consider the following situation where I have <code>df_a</code> and <code>df_b</code>.</p>
  <pre class="lang-r prettyprint-override"><code>library(tidyverse)
  
  # Dummy data A
  df_a &lt;- tibble(
     id = c(&quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;a&quot;),
     text = c(&quot;hi&quot;,&quot;why&quot;, &quot;bye&quot;,&quot;cry&quot;),
     created_date = c(as.Date(&quot;2020-01-01&quot;), as.Date(&quot;2020-02-02&quot;), as.Date(&quot;2020-03-01&quot;), as.Date(&quot;2020-04-04&quot;))
  )
  
  # Dummy data B
  df_b &lt;- tibble(
    id = c(&quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;a&quot;),
    group = c(&quot;GROUP A&quot;,&quot;GROUP B&quot;,&quot;GROUP C&quot;, &quot;GROUP C&quot;),
    start_date = c(as.Date(&quot;2020-01-01&quot;), as.Date(&quot;2020-01-01&quot;), as.Date(&quot;2020-01-01&quot;), as.Date(&quot;2020-02-04&quot;))
  )
  
  &gt; df_a
  # A tibble: 4 x 3
    id    text  created_date
    &lt;chr&gt; &lt;chr&gt; &lt;date&gt;      
  1 a     hi    2020-01-01  
  2 b     why   2020-02-02  
  3 c     bye   2020-03-01  
  4 a     cry   2020-04-04
  
  &gt; df_b
  # A tibble: 4 x 3
    id    group  start_date
    &lt;chr&gt; &lt;chr&gt;  &lt;date&gt;    
  1 a     GROUP A 2020-01-01
  2 b     GROUP B 2020-01-01
  3 c     GROUP C 2020-01-01
  4 a     GROUP C 2020-02-04
  
  # Current solution
  d_current_sol &lt;- df_a %&gt;% 
    left_join(
      df_b %&gt;% 
        distinct(id, .keep_all = T), by = &quot;id&quot;
      )
  
  &gt; d_current_sol
  # A tibble: 4 x 5
    id    text  created_date group  start_date
    &lt;chr&gt; &lt;chr&gt; &lt;date&gt;       &lt;chr&gt;  &lt;date&gt;    
  1 a     hi    2020-01-01   GROUP A 2020-01-01
  2 b     why   2020-02-02   GROUP B 2020-01-01
  3 c     bye   2020-03-01   GROUP C 2020-01-01
  4 a     cry   2020-04-04   GROUP A 2020-01-01
  
  # Desired solution
  &gt; d_desired
  # A tibble: 4 x 5
    id    text  created_date start_date group  
    &lt;chr&gt; &lt;chr&gt; &lt;date&gt;       &lt;date&gt;     &lt;chr&gt;  
  1 a     hi    2020-01-01   2020-01-01 GROUP A
  2 b     why   2020-02-02   2020-01-01 GROUP B
  3 c     bye   2020-03-01   2020-01-01 GROUP C
  4 a     cry   2020-04-04   2020-02-04 GROUP C
  
  </code></pre>
  <p>As you can see in <code>df_b</code> the start date for <code>id = a</code> is first <code>2020-01-01</code> and then <code>2020-02-04</code>.</p>
  <p>What I would like is for any row in <code>df_a</code> after <code>2020-02-04</code>, it's associated group to be <code>&quot;GROUP C&quot;</code>.  This is illustrated in the final data frame <code>d_desired</code></p>
  <p>However, with a traditional <code>left_join</code> where we match the row to a single row item, we only get the earliest entry (<code>d_current_sol</code>). This would be easy enough to do using <code>map()</code> and using a custom function with <code>mutate()</code>, however, SQL doesn't support that when translating <code>dplyr</code> queries.</p>
  <p>Does anyone know a way of doing this through the <code>tidyverse</code> paradigm? As I will be using <code>dbplyr</code> to run this on a PostgreSQL database.</p>