SQL Refactor
a UCPath example
Step-by-step guide
-
Employees job related information is stored in PS_JOB table in ODS
-
You should be using MAX effective date (EFFDT) and effective sequence(EFFSEQ)
SELECT J.* FROM HCM_ODS.PS_JOB J where
J.EFFDT = (SELECT MAX(J1.EFFDT) FROM HCM_ODS.PS_JOB J1
WHERE J.EMPLID = J1.EMPLID
AND J.EMPL_RCD = J1.EMPL_RCD
AND J1.EFFDT <= GETDATE())
AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ) FROM HCM_ODS.PS_JOB J2
WHERE J.EMPLID = J2.EMPLID
AND J.EMPL_RCD = J2.EMPL_RCD
AND J.EFFDT = J2.EFFDT)Original Query
SELECT j.*
FROM hcm_ods.ps_job J
WHERE j.effdt =
(
SELECT Max(j1.effdt)
FROM hcm_ods.ps_job J1
WHERE j.emplid = j1.emplid
AND j.empl_rcd = j1.empl_rcd
AND j1.effdt <= Getdate())
AND j.effseq =
(
SELECT Max(j2.effseq)
FROM hcm_ods.ps_job J2
WHERE j.emplid = j2.emplid
AND j.empl_rcd = j2.empl_rcd
AND j.effdt = j2.effdt) Reformatted
Demo time
SELECT j.emplid, j.empl_rcd, j.effdt, j.effseq
FROM hcm_ods.ps_job J
WHERE j.effdt =
(
SELECT Max(j1.effdt)
FROM hcm_ods.ps_job J1
WHERE j.emplid = j1.emplid
AND j.empl_rcd = j1.empl_rcd
AND j1.effdt <= Getdate()
)
AND j.effseq =
(
SELECT Max(j2.effseq)
FROM hcm_ods.ps_job J2
WHERE j.emplid = j2.emplid
AND j.empl_rcd = j2.empl_rcd
AND j.effdt = j2.effdt
) ;with
rankedJobs as (
select
emplid, empl_rcd, effdt, effseq
, rank = row_number() over (partition by emplid, empl_rcd order by
effdt desc, effseq desc)
from hcm_ods.PS_JOB
where effdt <= getdate()
)
select emplid, empl_rcd, effdt, effseq
from rankedJobs
where rank = 1
YMMV
SQL Refactor
By Kevin Wu
SQL Refactor
a SQL refactor exercise using UCPath latest and greatest job record
