I am working on a report that provides an audit trail for job record
changes. Each time an employee's job changes, a new row is added to
the job table for them. In this table, there are two date fields:
action date (action_dt) and effective date (effdt). The report pulls
any records by date where the effdt or action_dt is the date selected
and then pulls the previous record for that employee. People sometimes
have multiple changes on the same date. The problem with what I wrote
is that sometimes the maximum effective date or action date is greater
than the date selected, so the record gets excluded even though it
occurred on the date selected. I tried adding the (Action_Dt =
(:date) OR EFFDT = (:date)) at the end of the where clause, which then
includes the record on that date, but not on the previous one. Can
anyone help me on the best way to pull any records in my criteria plus
the last record for the employee? Thanks.
SELECT
EMPLOYEEID,
NAME,
EMPL_RCD_NBR,
EFFDT,
EFFSEQ,
DEPT,
JOB,
ACTION,
ACTION_DT,
ERank,
AMax,
EMax
FROM (SELECT
EMPLOYEEID,
NAME,
EMPL_RCD_NBR,
EFFDT,
EFFSEQ,
DEPT,
JOB,
ACTION,
ACTION_DT,
Dense_Rank () over (partition by EmployeeID order by EMPLOYEEID,
EMPL_RCD_NBR, EFFDT desc, EFFSEQ desc) As ERank,
Max(Action_Dt) over (partition by EmployeeID order by EMPLOYEEID,
EMPL_RCD_NBR, action_dt desc, EFFSEQ desc) As AMax,
Max(EFFDT) over (partition by EmployeeID order by EMPLOYEEID,
EMPL_RCD_NBR, effdt desc, EFFSEQ desc) As EMax
FROM (SELECT
EMPLOYEEID,
NAME,
EMPL_RCD_NBR,
EFFDT,
EFFSEQ,
DEPT,
JOB,
ACTION,
ACTION_DT
FROM PS_JOB
WHERE (Action_Dt <= (:date) OR EFFDT <= (:date))
AND a.PAYGROUP = 'R4X'
ORDER BY EMPLOYEEID, EMPL_RCD_NBR, EFFDT desc, EFFSEQ desc) a) b
WHERE b.RowNumber<=2 AND (b.AMax = (:date) OR b.EMax = (:date)) OR
(Action_Dt = (:date) OR EFFDT = (:date))
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
