我有一个数据表如下：

+--------+---------------+------------+------------+
| emp_id |  emp_job_nm   | start_date |  end_date  |
+--------+---------------+------------+------------+
| abc123 | cool_dude_1   | 03/02/2002 | 03/03/2020 |
| abc123 | cool_dude_2   | 03/03/2020 | 03/26/2020 |
| abc123 | not_cool_1    | 03/26/2020 | 04/27/2020 |
| abc123 | almost_cool_1 | 04/27/2020 | 05/27/2020 |
| def345 | cool_dude_1   | 05/27/2020 | 06/26/2020 |
| def345 | cool_dude_1   | 01/02/2020 | 01/03/2020 |
| def345 | cool_dude_2   | 01/03/2020 | 01/26/2020 |
| def345 | not_cool_1    | 02/26/2020 | 03/27/2020 |
| def345 | almost_cool_1 | 03/27/2020 | 07/27/2020 |
| def345 | cool_dude_1   | 07/27/2020 | 08/26/2020 |
+--------+---------------+------------+------------+

然后我有一个完整的日历表，它给了我1990年到2050年间每年的每一天的日期
我想要的结果是

+--------+-------------+------------+------------+------------+
| emp_id | emp_job_nm  | start_date |  end_date  |  cal_date  |
+--------+-------------+------------+------------+------------+
| abc123 | cool_dude_1 | 03/02/2002 | 03/03/2020 | 03/02/2020 |
| abc123 | cool_dude_1 | 03/02/2002 | 03/03/2020 | 03/03/2020 |
| abc123 | cool_dude_2 | 03/03/2020 | 03/26/2020 | 03/04/2020 |
| abc123 | cool_dude_2 | 03/03/2020 | 03/26/2020 | 03/05/2020 |
| abc123 | cool_dude_2 | 03/03/2020 | 03/26/2020 | 03/06/2020 |
+--------+-------------+------------+------------+------------+

基本上，当结束日期是<caldate时，请给我id和作业名称。需要注意的是，这是一个连接，因为这是两个独立的表。所以前4列在employee表中，calu date在calendar表中。延长这一期限，只要结束日期<=校准日期，每个员工每天都要按职务排序。
得到正确的连接和重复数据消除是踢我的屁股。
我尝试了一些不同的排名超过功能，但不能得到重复消除工作。

RANK () OVER ( PARTITION BY emp_id, emp_job_nm, end_date ORDER BY cal_date ASC ) AS rownum
RANK () OVER ( PARTITION BY emp_id, emp_job_nm, end_date ORDER BY end_date ASC ) AS rownum

作为…的一部分

dates AS (
    SELECT TO_DATE(calendar_date,'YYYY-MM-DD') AS cal_date
    FROM iso_calendar.date_dim
)

SELECT *, RANK () OVER ( PARTITION BY emp_id, emp_job_nm, end_date ORDER BY cal_date ASC ) AS rownum
FROM dates d
LEFT JOIN emp e ON ON e.end_date <= cal_date AND e.start_date < e.end_date