Position History

Are you using Assignment date in PA13? If so you could use DATE_ASSIGN in the PAEMPPOS table since that doesn't change when salary changes ... it's been helpful for me when I've had to pull that.

Thanks for your response Mary. I checked, and it doesn't look like we are using that field.

Not sure if this is what you are looking for but if you have the report sorted ascending by the "Beg_Date" and you use a formula with previous(Beg_Date) it will let you use the previous Beg_Date to calculate the length of time in a position. [The last column shows what you would see with a formula showing previous(Beg_Date).]

Beg Date 9/21/2008, Position 0326, NULL
Beg Date 3/24/13, Position 3564, 9/21/2008
Beg Date 6/1/2013, Position 0194, 3/24/2013

However, to make this work you might want to think a bit about how to do the formulas to adjust for the first and last records and to show the title with the right information. You can use the previous() for other fields to display information on the next line if needed.

Good luck!

I have a SQL statement that pulls this type of report. It "cleans up" the repeat records in PAEMPPOS. Are you on Oracle?

I would like a copy of the SQL statement please. patriica.goforth@msj.org

SELECT jd.company
,jd.employee
,jd.last_name
,jd.first_name
,jd.adj_hire_date
,jd.date_hired
,jd.term_date
,jd.status
,jd.position
,jd.job_code
,jd.title
,jd.process_level
,jd.department
,jd.dept_name
,jd.effect_date
,jd.job_end_date
,ROUND((MONTHS_BETWEEN(NVL(jd.job_end_date,SYSDATE),jd.effect_date))/12,2) as years_in_job
,jd.prev_position
,jd.prev_job_code
,jd.prev_title
,jd.prev_process_level
,jd.prev_department
,jd.prev_dept_name
FROM (SELECT po.*
,CASE WHEN (CASE WHEN po.end_date = to_date('01011700','mm/dd/yyyy') THEN NULL
ELSE (LEAD(po.effect_date, 1)
OVER(PARTITION BY po.employee ORDER BY po.effect_date))-1 END) IS NULL
THEN po.term_date
ELSE (CASE WHEN po.end_date = to_date('01011700','mm/dd/yyyy') THEN NULL
ELSE (LEAD(po.effect_date, 1)
OVER(PARTITION BY po.employee ORDER BY po.effect_date))-1 END) END as job_end_date
FROM (SELECT p.*
,LAG(p.position, 1) OVER(PARTITION BY p.employee ORDER BY p.effect_date) as prev_position
,LAG(p.fte, 1) OVER(PARTITION BY p.employee ORDER BY p.effect_date) as prev_fte
,LAG(p.job_code, 1) OVER(PARTITION BY p.employee ORDER BY p.effect_date) as prev_job_code
,LAG(p.title, 1) OVER(PARTITION BY p.employee ORDER BY p.effect_date) as prev_title
,LAG(p.process_level, 1) OVER(PARTITION BY p.employee ORDER BY p.effect_date) as prev_process_level
,LAG(p.department, 1) OVER(PARTITION BY p.employee ORDER BY p.effect_date) as prev_department
,LAG(p.dept_name, 1) OVER(PARTITION BY p.employee ORDER BY p.effect_date) as prev_dept_name
FROM (SELECT p.company
,p.employee
,trim(e.last_name) as last_name
,trim(e.first_name) as first_name
,e.adj_hire_date
,e.date_hired
,NULLIF(e.term_date, to_date('01011700','mm/dd/yyyy')) as term_date
,e.emp_status as status
,p.position
,p.pos_level
,p.fte
,p.effect_date
,p.end_date
,p.job_code
,j.description as title
,p.process_level
,p.department
,d.r_name as dept_name
FROM paemppos p
JOIN jobcode j ON (p.job_code = j.job_code)
JOIN deptcode d ON (p.process_level = d.process_level AND
p.department = d.department)
JOIN employee e ON (p.employee = e.employee)
WHERE pos_level = 1
ORDER BY p.employee,p.pos_level,p.effect_date) p) po
WHERE po.job_code <> po.prev_job_code
OR po.prev_job_code IS NULL) jd
--Created By Dave Curtis
--Jan-2012
--This SQL statement is intended to be used to pull job history and provide the length of
--time spent in a specific job
--the statement uses PAEMPPOS to pull the history
--The statement is set up to only pull position level 1

This is the basic SQL statement used. I alter it as needed, - like when I need to run a report that shows how long someone was in a specific position (defined as in a specific job within a specific department) - then I alter the SQL so the WHERE clause at the bottom is
WHERE (po.process_level <> po.prev_process_level
OR po.prev_process_level IS NULL)
OR (po.department <> po.prev_department
OR po.prev_department IS NULL)
OR (po.job_code <> po.prev_job_code
OR po.prev_job_code IS NULL)

Depending on how you use positions and how/if you use multiple positions, you may also want to change the SQL so it includes position levels other than 1. This can add another level of complexity to it, but it can be altered to pull in multiple positions as well.

Feel free to e-mail me if you have questions about the SQL statement I posted. I would be more than willing to help if I can.

dave.curtis.theq@gmail.com

PattyG - I tried to e-mail you the statement but my e-mail kept returning the e-mail saying it was not able to deliver the e-mail.

can you tell me the table name? patricia.goforth@msj.org

Mis-spelled my name last reply

PAEMPPOS is the table