Simplifying a sql query

Hello -  

I am troubleshooting why a report is generating a duplicate record.  So the person's sql I am looking at is as follows.  Please note I find the bolded sql rather confusing.  How can the bolded portion be written in a manner that is more straightforward. Please explain your answer.


 SELECT  'LN' ,
 'Test' ,
 '2020-02-12' ,
 'V' ,
 A.REG_TEMP ,
 A.POSITION_NBR ,
 A.POSN_STATUS ,
 A.DESCR ,
 A.DEPTID ,
 B.DESCR,
 A.SAL_ADMIN_PLAN,
 CASE WHEN C.EMPLID IS NULL THEN ' ' ELSE C.EMPLID END AS EMPLID2 /* Current Incumbent EMPLID */
 ,0
 /* Last Incumbent EMPLID */
 ,D.REPORTS_TO
 ,''
 ,''
 /* Current INCUMBENT NAME */
 ,''
 /* Last INCUMBENT NAME */
 ,''
 ,''
 ,''
 ,GETDATE()
 ,CASE WHEN A.GRADE IS NULL THEN ' ' ELSE A.GRADE END

 [b]FROM 
 ( SELECT DISTINCT D2.SETID ,A2.DEPTID ,A2.POSITION_NBR ,A2.DESCR ,
 A2.POSN_STATUS ,A2.STATUS_DT ,A2.BUSINESS_UNIT ,
 A2.JOBCODE ,A2.COMPANY ,A2.LOCATION ,A2.FULL_PART_TIME ,
 A2.REG_TEMP ,A2.CONFIDENTIAL_POSN ,A2.REPORTS_TO ,
 A2.MAX_HEAD_COUNT ,A2.DESCRLONG ,A2.MANAGER_LEVEL ,A2.SAL_ADMIN_PLAN ,A2.STEP ,A2.GRADE 
 FROM PS_POSITION_DATA A2 , PS_DEPT_TBL D2 
 WHERE D2.SETID=( SELECT Z2.SETID FROM PS_SET_CNTRL_REC Z2 
 WHERE Z2.SETCNTRLVALUE=A2.BUSINESS_UNIT AND Z2.RECNAME='DEPT_TBL') 
 AND D2.DEPTID=A2.DEPTID 
 AND D2.EFFDT = ( SELECT MAX(E2.EFFDT) FROM PS_DEPT_TBL E2 
 WHERE E2.DEPTID = D2.DEPTID AND E2.SETID=D2.SETID AND E2.EFFDT<='2020-02-12')
 AND A2.EFF_STATUS = 'A' 
 AND A2.BUDGETED_POSN = 'Y' 
 AND A2.EFFDT = ( SELECT MAX(B2.EFFDT) 
 FROM PS_POSITION_DATA B2 WHERE A2.POSITION_NBR = B2.POSITION_NBR 
 AND B2.EFFDT <= '2020-02-12') 
 AND A2.MAX_HEAD_COUNT > ( SELECT COUNT(*) 
 FROM ( SELECT * FROM PS_JOB A3 
 WHERE A3.EMPL_STATUS IN('A','P','L','S')
 AND A3.HR_STATUS NOT IN ('I')
 AND A3.POSITION_NBR <> ' ' 
 AND A3.EFFDT = ( SELECT MAX(C3.EFFDT) FROM PS_JOB C3 
 WHERE C3.EMPLID = A3.EMPLID AND C3.EMPL_RCD = A3.EMPL_RCD 
 AND C3.EFFDT <= '2020-02-12') AND A3.EFFSEQ = ( SELECT MAX(D3.EFFSEQ) 
 FROM PS_JOB D3 WHERE D3.EMPLID = A3.EMPLID AND D3.EMPL_RCD = A3.EMPL_RCD 
 AND D3.EFFDT = A3.EFFDT) ) [/b]C4 
 WHERE A2.POSITION_NBR = C4.POSITION_NBR)) A 
 LEFT JOIN PS_DEPT_TBL B ON B.SETID = A.SETID 
 AND B.DEPTID = A.DEPTID AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) 
 FROM PS_DEPT_TBL B_ED WHERE B_ED.SETID = B.SETID 
 AND B_ED.DEPTID = B.DEPTID AND B.EFF_STATUS = 'A') 
 LEFT JOIN PS_POSN_HISTORY C ON C.POSITION_NBR = A.POSITION_NBR 
 AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_POSN_HISTORY C_ED 
 WHERE C_ED.POSITION_NBR = A.POSITION_NBR AND C.EFFDT<='2020-02-12') 
 LEFT JOIN PS_POSITION_DATA D ON A.POSITION_NBR = D.POSITION_NBR 
 AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_POSITION_DATA D_ED 
 WHERE D_ED.POSITION_NBR = D.POSITION_NBR AND D_ED.EFFDT<='2020-02-12' 
 AND D_ED.EFF_STATUS = 'A') WHERE A.SAL_ADMIN_PLAN IN('FSS', 'FAC', 'FAD') 
 AND A.SETID = 'DC' AND A.STATUS_DT<='2020-02-12'
 AND A.POSITION_NBR = '10000938'

Select allOpen in new window

Please explain your solution for a straight forward person such as I :-)

Thanks in advance,
Brock

P.S. I know that the field HR_STATUS = 'I' belonging to the PS_JOB table should assist in reducing the duplicity but at this time of day it 'ain't ' happening for me :-)  and I am confused by SQL.

I am off to my commute now... no data plan on my phone - shall check in tomorrow.  If no solution is present, I shall in my own dogged way pursue my solution :-)  Have a great evening.