SQL Query to get future dated records(ALL) along with current dated records

Question

Reposting the question with better examples-

I have a table per_asg that has data like -

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

Now when I use the below query -

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

I get the current data correctly. But I want to add a condition instead of the trunc(sysdate) to fetch ALL the future dates too i.e. 16-Jun-2020 also in the above query i.e. as of sysdate and future date. how can i change the above query to do so ?

OUTPUT I want i.e. trunc(sysdate) and any date i.e. greater than today's date should come in the output-

  person_number  Full Name
    45             XYZ
    47             ABC
    49             TYI

Tip of today: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed — jarlh

Usagi Miyamoto Usagi Miyamoto · Accepted Answer · 2020-06-05T14:32:56

Try something like this:

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

Only checking the end date to be greater than or equal to the current date.

EDIT: Added DISTINCT to remove duplicated rows.

SQL Query to get future dated records(ALL) along with current dated records

1 Answers