Преобразование/преобразование SQL в левое соединение dplyr с несколькими критериями

Вот установка (это на самом деле не так сложно...):

Таблица Названия должностей

| PersonID | JobTitle | StartDate | EndDate |
|----------|----------|-----------|---------|
| A        | A1       | 1         | 5       |
| A        | A2       | 6         | 10      |
| A        | A3       | 11        | 15      |
| B        | B1       | 2         | 4       |
| B        | B2       | 5         | 7       |
| B        | B3       | 8         | 11      |
| C        | C1       | 5         | 12      |
| C        | C2       | 13        | 14      |
| C        | C3       | 15        | 18      |

Таблица Транзакции:

| PersonID | TransDate | Amt |
|----------|-----------|-----|
| A        | 2         | 5   |
| A        | 3         | 10  |
| A        | 12        | 5   |
| A        | 12        | 10  |
| B        | 3         | 5   |
| B        | 3         | 10  |
| B        | 10        | 5   |
| C        | 16        | 10  |
| C        | 17        | 5   |
| C        | 17        | 10  |
| C        | 17        | 5   |

Желаемый результат:

| PersonID | JobTitle | StartDate | EndDate | Amt |
|----------|----------|-----------|---------|-----|
| A        | A1       | 1         | 5       | 15  |
| A        | A2       | 6         | 10      | 0   |
| A        | A3       | 11        | 15      | 15  |
| B        | B1       | 2         | 4       | 15  |
| B        | B2       | 5         | 7       | 0   |
| B        | B3       | 8         | 11      | 5   |
| C        | C1       | 5         | 12      | 0   |
| C        | C2       | 13        | 14      | 0   |
| C        | C3       | 15        | 18      | 30  |

Этот SQL дает мне желаемый результат:

select jt.PersonID, jt.JobTitle, jt.StartDate, jt.EndDate, coalesce(sum(amt), 0) as amt
from JobTitles jt left join
     Transactions t
     on jt.PersonId = t.PersonId and
        t.TransDate between jt.StartDate and jt.EndDate
group by jt.PersonID, jt.JobTitle, jt.StartDate, jt.EndDate;

Таблицы для R:

JobTitles <- structure(list(PersonID = structure(c(1L, 1L, 1L, 2L, 2L, 2L, 
3L, 3L, 3L), .Label = c("A", "B", "C"), class = "factor"), JobTitle = structure(1:9, .Label = c("A1", 
"A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3"), class = "factor"), 
    StartDate = c(1L, 6L, 11L, 2L, 5L, 8L, 5L, 13L, 15L), EndDate = c(5L, 
    10L, 15L, 4L, 7L, 11L, 12L, 14L, 18L)), .Names = c("PersonID", 
"JobTitle", "StartDate", "EndDate"), class = "data.frame", row.names = c(NA, 
-9L))
Transactions <- structure(list(PersonID = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 
2L, 3L, 3L, 3L, 3L), .Label = c("A", "B", "C"), class = "factor"), 
    TransDate = c(2L, 3L, 12L, 12L, 3L, 3L, 10L, 16L, 17L, 17L, 
    17L), Amt = c(5L, 10L, 5L, 10L, 5L, 10L, 5L, 10L, 5L, 10L, 
    5L)), .Names = c("PersonID", "TransDate", "Amt"), class = "data.frame", row.names = c(NA, 
-11L))

Как перевести SQL в рабочий код dplyr? Я застреваю на left_join:

left_join(JobTitles, Transactions, 
          by = c("PersonID" = "PersonID", 
                 "StartDate" < "TransDate",
                 "EndDate" >= "TransDate"))
# Error: cannot join on columns 'TRUE' x '' : index out of bounds