Thursday, November 26, 2015

Custom SORT in SQLs

SELECT A.EMPLID,A.EMPL_RCD,(SELECT PER.NAME_DISPLAY FROM PS_PERSONAL_DATA PER WHERE PER.EMPLID=A.EMPLID) AS 'NAME',
A.GP_PAYGROUP,(SELECT TOP 1 XLATLONGNAME FROM PSXLATITEM WHERE EFF_STATUS='A' AND FIELDNAME ='BAS_PROCESS_STATUS' AND FIELDVALUE=PAR.BAS_PROCESS_STATUS ORDER BY EFFDT DESC)'CURRENT STATUS'
FROM PS_JOB A  JOIN PS_BAS_PARTIC PAR ON A.EMPLID=PAR.EMPLID AND
A.EMPL_RCD=PAR.EMPL_RCD WHERE A.BENEFIT_SYSTEM='BA'
AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID
                     AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT<='01-JAN-2016'
                     )
AND A.EFFSEQ =(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.HR_STATUS ='A'
AND A.BAS_GROUP_ID ='XYZ'
AND A.PER_ORG='EMP'
AND A.GP_PAYGROUP LIKE 'XYZ%'
AND PAR.SCHED_ID ='XYZ'
ORDER BY
CASE WHEN PAR.BAS_PROCESS_STATUS = 'AS' THEN 1  
     WHEN PAR.BAS_PROCESS_STATUS = 'PR' THEN 2
     WHEN PAR.BAS_PROCESS_STATUS = 'NT' THEN 3
      WHEN PAR.BAS_PROCESS_STATUS = 'ET' THEN 4
     WHEN PAR.BAS_PROCESS_STATUS = 'FE' THEN 5
END ASC

No comments:

Post a Comment