Query Manager: Trying to bring in the Manager's name

Question

I'm using PeopleSoft's Query Manager to build a new hire report that brings in the manager's name. Unfortunately the Job - EE table captures the reports to position and not the manager. In an effort to get the manager's name I have to link to the Job - EE table again, linking the two tables with A.Reports_To equal to G.Position_NBR. So now I have two views of the Job - EE table. The problem is that I'm looking at a date range (Jan 1 2018 to present) and I haven't been able to figure out how to bring in the manager at precisely the same time as the hire date. I seem to be bringing in all the managers who have been in the Reports_To position prior to the ending effective date. I think this is because the Job - EE table links to the position (reports to) instead of an actual employee. The person occupying the reports to often changes.

Anyone have any suggestions?
EDITED to update: Here are some pics of the various tables, query manager criteria, etc:

Here's the SQL:

    SELECT DISTINCT A.EMPLID, B.NAME, (CONVERT(CHAR(10),A.EFFDT,121)), A.DEPTID, D.DESCR, C.DESCR, A.ANNUAL_RT, E.DESCR, G.EMPLID, G.POSITION_NBR, Concat( H.FIRST_NAME,' ', H.LAST_NAME) 
  FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1, PS_PERSONAL_DATA B, PS_DEPT_TBL D, PS_SET_CNTRL_REC D2, PS_JOBCODE_TBL C, PS_SET_CNTRL_REC C2, PS_LOCATION_TBL E, PS_SET_CNTRL_REC E2, PS_JOB G, PS_EMPLMT_SRCH_QRY G1, PS_NAMES H, PS_PERALL_SEC_QRY H1 
  WHERE ( D.DEPTID = A.DEPTID 
    AND D2.SETCNTRLVALUE = A.BUSINESS_UNIT 
    AND D2.RECNAME = 'DEPT_TBL' 
    AND D2.SETID = D.SETID 
    AND C.JOBCODE = A.JOBCODE 
    AND C2.SETCNTRLVALUE = A.BUSINESS_UNIT 
    AND C2.RECNAME = 'JOBCODE_TBL' 
    AND C2.SETID = C.SETID 
    AND E.LOCATION = A.LOCATION 
    AND E2.SETCNTRLVALUE = A.BUSINESS_UNIT 
    AND E2.RECNAME = 'LOCATION_TBL' 
    AND E2.SETID = E.SETID 
    AND A.EMPLID = A1.EMPLID 
    AND A.EMPL_RCD = A1.EMPL_RCD 
    AND A1.OPRID = 'XXXXXXXX' 
    AND G.EMPLID = G1.EMPLID 
    AND G.EMPL_RCD = G1.EMPL_RCD 
    AND G1.OPRID = 'XXXXXXXX' 
    AND H.EMPLID = H1.EMPLID 
    AND H1.OPRID = 'XXXXXXXX' 
    AND ( A.EFFDT = (SELECT MIN( F.EFFDT) 
  FROM PS_JOB F, PS_EMPLMT_SRCH_QRY F1 
  WHERE F.EMPLID = F1.EMPLID 
    AND F.EMPL_RCD = F1.EMPL_RCD 
    AND F1.OPRID = 'XXXXXXXX' 
    AND ( A.EMPLID = F.EMPLID 
     AND A.EMPL_RCD = F.EMPL_RCD 
     AND A.ACTION IN ('HIR','REH','REI') 
     AND A.EFFDT BETWEEN :1 AND :2 )) 
     AND A.ACTION IN ('HIR','REH','REI') 
     AND B.EMPLID = A.EMPLID 
     AND D.EFFDT = 
        (SELECT MAX(D_ED.EFFDT) FROM PS_DEPT_TBL D_ED 
        WHERE D.SETID = D_ED.SETID 
          AND D.DEPTID = D_ED.DEPTID 
          AND D_ED.EFFDT <= A.EFFDT) 
     AND C.EFFDT = 
        (SELECT MAX(C_ED.EFFDT) FROM PS_JOBCODE_TBL C_ED 
        WHERE C.SETID = C_ED.SETID 
          AND C.JOBCODE = C_ED.JOBCODE 
          AND C_ED.EFFDT <= A.EFFDT) 
     AND E.EFFDT = 
        (SELECT MAX(E_ED.EFFDT) FROM PS_LOCATION_TBL E_ED 
        WHERE E.SETID = E_ED.SETID 
          AND E.LOCATION = E_ED.LOCATION 
          AND E_ED.EFFDT <= A.EFFDT) 
     AND A.REPORTS_TO = G.POSITION_NBR 
     AND G.EMPLID = H.EMPLID 
     AND H.EFFDT = 
        (SELECT MAX(H_ED.EFFDT) FROM PS_NAMES H_ED 
        WHERE H.EMPLID = H_ED.EMPLID 
          AND H.NAME_TYPE = H_ED.NAME_TYPE) 
     AND G.EFFDT = 
        (SELECT MAX(G_ED.EFFDT) FROM PS_JOB G_ED 
        WHERE G.EMPLID = G_ED.EMPLID 
          AND G.EMPL_RCD = G_ED.EMPL_RCD 
          AND G_ED.EFFDT <= ( A.LAST_HIRE_DT)+1) 
    AND G.EFFSEQ = 
        (SELECT MAX(G_ES.EFFSEQ) FROM PS_JOB G_ES 
        WHERE G.EMPLID = G_ES.EMPLID 
          AND G.EMPL_RCD = G_ES.EMPL_RCD 
          AND G.EFFDT = G_ES.EFFDT) ))

Answer 1

In addition to this being a lot easier if your institution/company would just use the SUPERVISOR_ID field, it seems your problem is:

I seem to be bringing in all the managers who have been in the Reports_To position prior to the ending effective date

This is because, in your initial select on PS_JOB(A), you only limit the effective date(EFFDT) to your input range [:1,:2] for records with an action in ('HIR','REH','REI').

 AND ( A.EFFDT BETWEEN :1 AND :2  
 AND A.ACTION IN ('HIR','REH','REI')
 ... )

This will select every record with an EFFDT in the interval, while you only want one, presumably the first(MIN).

AND ( A.EFFDT = (SELECT MIN(A_ED.EFFDT) FROM PS_JOB A_ED
                    WHERE A_ED.EMPLID = A.EMPLID
                      AND A_ED.EMPL_RCD = A.EMPL_RCD
                      AND A.ED.EFFDT BETWEEN :1 AND :2
                      AND A_ED.ACTION IN ('HIR', 'REH', 'REI')
... ) 

This will probably fix the problem, but keep in mind you may also have to account for the effective sequence (EFFSEQ) to filter one record out.

EDIT: Via Query Manager

1. Go to criteria and edit the A.EFFDT criterium.
2. Condition type should be 'equal to' and Expression 2 type should be subquery.
3. Define/Edit subquery
4. Select Job EE as record, click OK on the messagebox about effective date.
5. Select EFFDT as field under Query tab, Edit field and select Min aggregate. Here you get the error about 'aggregate field but is being used in non-having criteria'
6. Go to Criteria tab, delete the auto added effdt criterium and add the other criteria, matching the subquery to the main query and the ACTION criteria. 1