用于获取未来日期记录 (ALL) 以及当前日期记录的 SQL 查询

Question

Reposting the question with better examples-

我有一个表格per_asg其中包含以下数据 -

asg_number         asg_status         dept            eff_start_date         eff_end_date   person_number
E45                 Active            Software        01-Feb-2020            28-Aug-2020      45
E45                 Active            IT              29-Aug-2020            31-Dec-4712      45 
E47                 Active            IT              16-Jun-2020            31-Dec-4712      47
E49                 Active            IT              20-Jun-2020            31-Dec-4712      49    
per_people
person_number  eff_start_date          eff_end_date        Full Name
45             01-feb-2020             28-Aug-2020         XYZ
45             29-Aug-2020             31-Dec-4712          XYZ
47             16-Jun-2020             31-Dec-4712          ABC
49             20-Jun-2020             31-Dec-4712          TYI

现在当我使用以下查询时 -

select papf.person_number, papf.full_name
from per_asg asg, per_people papf
where asg.person_number = papf.person_number
and trunc(sysdate) between papf.eff_start_Date and papf.eff_end_date
and trunc(sysdate) between asg.eff_start_Date and asg.eff_end_date
Output i get from the query- 
person_number  Full Name
45             XYZ

我正确获取了当前数据。但是我想添加一个条件而不是 trunc(sysdate( 来获取所有未来的日期，即 16-Jun-2020 也在上面的查询中，即截至系统日期和未来日期。如何更改上述查询以执行此操作？

输出我想要，即 trunc(sysdate(和任何日期，即大于今天的日期应该出现在输出中-

person_number  Full Name
45             XYZ
47             ABC
49             TYI

Answer 1

尝试这样的事情：

SELECT DISTINCT papf.person_number, papf.full_name
FROM per_asg asg, per_people papf
WHERE asg.person_number = papf.person_number
AND trunc(sysdate) <= papf.eff_end_date
AND trunc(sysdate) <= asg.eff_end_date

仅检查结束日期是否大于或等于当前日期。

编辑：添加了删除重复行DISTINCT。