Department Changes

[tcimis]

We are on PS 8.9, Oracle Database. I am trying to write a query to track department changes. The action used is not always department change. I am joining Job to Employees and selecting where employees.deptid <> job.deptid. This works fine with the exception of when multiple changes are made on the same date:

For example:
Employee A has a supervisor change on 5/16 with a seq 0, a pay rate change with a seq 1 and a department change with a seq 2. The current record is dated 7/25 with a location change with a seq 0. With the query the way I currently have it, it appears the department change was done on 7/25 because the current record department <> the previous records seq 1. How do I write the query so that it compares the 5/16 seq 1 record with the 5/16 seq 2 record? I only have access to the PeopleSoft query tool so I am also constrained by the search view.

Hope this makes sense.

Thanks for your assistance.

 

[notadba]

Hi TCMIS,

This kind of thing can be done - but you need to get into writing expressions. If you take the default max effdt and seqeunce logic, then this will basically screw up. What you can do is compare the date and sequence together by creating your own subquery on job.

Basic syntax would look like (untested as I don't have an Oracle instance with PSQuery available)

SELECT A.EMPLID
, A.EFFDT
, A.EFFSEQ
, A.DEPTD
, B.EFFDT
, B.EFFSEQ
, B.DEPTID
FROM PS_JOB A
, PS_JOB B
-- GET CURRENT ROW)
WHERE 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 <= SYSDATE)
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_ES.EFFDT = A.EFFDT)
--FIND MAX ROW WHERE DEPT DIFFERENT
AND A.EMPLID = B.EMPLID
AND A.EMPL_RCD = B.EMPL_RCD
AND CHAR(B.EFFDT)||CHAR(B.EFFSEQ) = (SELECT MAX(CHAR(C.EFFDT)||CHAR(C.EFFSEQ))
FROM PS_JOB C
WHERE B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
AND C.DEPTD <> A.DEPTID
AND CHAR(C.EFFDT)||CHAR(C.EFFSEQ) <= CHAR(A.EFFDT)||CHAR(A.EFFSEQ))

Note this code will work ok where your date format is 'YYYY-MM-DD', so you will need to look
at a to_char function on your dates to get this to work. Based on the above format, the convert/concatenate
would take your 2009-07-25 date and sequence 0 and create a string value of "2009-07-250" this would then be compared
against the strung values of "2009-05-161" AND "2009-05-162". This would return the sequence 2 record on 5/16.

Hope this gets you pointed in the right direction.

 

[tcimis]

Thank you for your assistance. I will let you know how it goes.

 

[tcimis]

This still isn't exactly what I want. Please see below: Thank you for your assistance.

The query returns:

ID Eff Date Sequence DeptID Eff Date Sequence DeptID
12345 7/25/2009 0 B1B014A000 2/10/2007 0 B1B0014A00

The history for the employee is as follows:


ID Eff Date Sequence DeptID
12345 11/27/2006 0 3E4114B000
12345 12/30/2006 0 3E4114B000
12345 2/10/2007 0 B1B0014A00
12345 4/7/2007 0 B1B0014A00
12345 12/1/2007 0 B1B014A000
12345 12/29/2007 0 B1B014A000
12345 12/29/2007 1 B1B014A000
12345 4/5/2008 0 B1B014A000
12345 5/31/2008 0 B1B014A000
12345 8/7/2008 0 B1B014A000
12345 1/8/2009 0 B1B014A000
12345 4/4/2009 0 B1B014A000
12345 6/27/2009 0 B1B014A000
12345 7/25/2009 0 B1B014A000


What I would like to see is that the last DeptID change took place on 12/1/2007.

Any ideas?

 

[notadba]

I see my deliberate mistake ;-)

I wrote this from job perspective - whereas employees already resolves the current deptid. Modified code below.

What I am guessing is happening is that you have not converted the date format to 'YYYY-MM-DD'.

SO what is happening is that it is finding the max date/sequence where the deptid is different - but
is using the strung version of the date as MM/DD/YYYY - so it is finding that 2/10/2007 is greater than
4/7/2007..

Query gets a little fiddly with the date casting - as it will impose its own to_char function in the sql
generated. So you will need to work at the expressions to get it to format correctly for this calc.

The other issue is that I have looked for the max row where the dept is different - whereas you are looking
for the min entry point for the current department. You can either do this by changing the subquery to look
for the min date/sequence where the deptid = current deptid, or if you have movement in and out of a deptid
multiple times, you need to use the basic query I provided before, and add another subquery that then looks for the next date/sequence after the row found in the first subquery.
-------------------
Once again - untested code as no instance running.

SELECT A.EMPLID
, A.EFFDT
, A.EFFSEQ
, A.DEPTD
, C.EFFDT
, C.EFFSEQ
, C.DEPTID
FROM PS_EMPLOYEES A
, PS_JOB B
, PS_JOB C
-- GET CURRENT ROW)
WHERE A.EMPLID = B.EMPLID
AND A.EMPL_RCD = B.EMPL_RCD
--FIND MAX ROW WHERE DEPT DIFFERENT
AND TO_CHAR('YYYY-MM-DD',B.EFFDT)||CHAR(B.EFFSEQ) = (SELECT MAX(TO_CHAR('YYYY-MM-DD',C.EFFDT)||CHAR(C.EFFSEQ))
FROM PS_JOB C
WHERE B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
AND C.DEPTD <> A.DEPTID
AND TO_CHAR('YYYY-MM-DD',C.EFFDT)||CHAR(C.EFFSEQ) <= TO_CHAR('YYYY-MM-DD',A.EFFDT)||CHAR(A.EFFSEQ))
--FIND MIN ROW OF CURRENT DEPTID
AND B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
AND TO_CHAR('YYYY-MM-DD',C.EFFDT)||CHAR(C.EFFSEQ) = (SELECT MAX(TO_CHAR('YYYY-MM-DD',D.EFFDT)||CHAR(D.EFFSEQ))
FROM PS_JOB D
WHERE C.EMPLID = D.EMPLID
AND C.EMPL_RCD = D.EMPL_RCD
AND TO_CHAR('YYYY-MM-DD',D.EFFDT)||CHAR(D.EFFSEQ) > TO_CHAR('YYYY-MM-DD',B.EFFDT)||CHAR(B.EFFSEQ))


----------------------------------------
SO TABLE A RETURNS CURRENT DEPTID
12345 7/25/2009 0 B1B014A000

SUBQUERY - TABLE B GETS MAX ROW <> CURRENT DEPTID
12345 4/7/2007 0 B1B0014A00

SUBQUERY - TABLE D - GETS MIN ROW > TABLE B (MOST RECENT ENTRY DATE
TO CURRENT DEPTID)
12345 12/1/2007 0 B1B014A000

----------------------------------------

As a cheat - you could try using the DEPT_ENTRY_DT on job/employees as a quick workaround -
though will not cater for multiple entries to same deptid.

 

[tcimis]

I am close but not quite there. I am showing the output and the sql code. I'm hoping you can see where I messed up. In your sample code you had PS_JOB C twice and I don't think I translated it properly so I didn't have 2 PS_JOB C's. I also had issues with the to_char so I used substr to get the date in the order needed. The 2/10/07 date needs to be 12/1/07. I appreciate all your assistance.



ID A.Eff Date A.Sequence A.DeptID D.Eff Date D.Sequence D.DeptID B.Eff Date B.Sequence B.DeptID
12345 7/25/2009 0 B1B014A000 4/7/2007 0 B1B0014A00 2/10/2007 0 B1B0014A00


SELECT A.EMPLID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ, A.DEPTID, TO_CHAR(D.EFFDT,'YYYY-MM-DD'), D.EFFSEQ, D.DEPTID, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), B.EFFSEQ, B.DEPTID
FROM PS_EMPLOYEES A, PS_EMPLMT_SRCH_QRY A1, PS_JOB B, PS_EMPLMT_SRCH_QRY B1, PS_JOB D, PS_EMPLMT_SRCH_QRY D1
WHERE A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = '14785'
AND B.EMPLID = B1.EMPLID
AND B.EMPL_RCD = B1.EMPL_RCD
AND B1.OPRID = '14785'
AND D.EMPLID = D1.EMPLID
AND D.EMPL_RCD = D1.EMPL_RCD
AND D1.OPRID = '14785'
AND ( A.EMPLID = B.EMPLID
AND A.EMPL_RCD = B.EMPL_RCD
AND substr( B.EFFDT,1,4)||substr( B.EFFDT,6,2)||substr( B.EFFDT,9,2)||TO_CHAR( B.EFFSEQ) = (SELECT MAX(substr( C.EFFDT,1,4)||substr( C.EFFDT,6,2)||substr( C.EFFDT,9,2)||TO_CHAR( C.EFFSEQ))
FROM PS_JOB C, PS_EMPLMT_SRCH_QRY C1
WHERE C.EMPLID = C1.EMPLID
AND C.EMPL_RCD = C1.EMPL_RCD
AND C1.OPRID = '14785'
AND ( C.EMPLID = B.EMPLID
AND C.EMPL_RCD = B.EMPL_RCD
AND C.DEPTID <> A.DEPTID
AND substr( C.EFFDT,1,4)||substr( C.EFFDT,6,2)||substr( C.EFFDT,9,2)||TO_CHAR( C.EFFSEQ) < substr( A.EFFDT,1,4)||substr( A.EFFDT,6,2)||substr( A.EFFDT,9,2)||TO_CHAR( A.EFFSEQ)
AND substr( C.EFFDT,1,4)||substr( C.EFFDT,6,2)||substr( C.EFFDT,9,2)||TO_CHAR( C.EFFSEQ) = (SELECT MIN(substr( E.EFFDT,1,4)||substr( E.EFFDT,6,2)||substr( E.EFFDT,9,2)||TO_CHAR( E.EFFSEQ))
FROM PS_JOB E, PS_EMPLMT_SRCH_QRY E1
WHERE E.EMPLID = E1.EMPLID
AND E.EMPL_RCD = E1.EMPL_RCD
AND E1.OPRID = '14785'
AND ( E.EMPLID = D.EMPLID
AND E.EMPL_RCD = D.EMPL_RCD
AND substr( E.EFFDT,1,4)||substr( E.EFFDT,6,2)||substr( E.EFFDT,9,2)||TO_CHAR( E.EFFSEQ) > substr( D.EFFDT,1,4)||substr( D.EFFDT,6,2)||substr( D.EFFDT,9,2)||TO_CHAR( D.EFFSEQ)
AND E.DEPTID = D.DEPTID )) ))
AND B.EMPLID = D.EMPLID
AND B.EMPL_RCD = D.EMPL_RCD
AND A.EMPLID = '12345' )

 

[notadba]

Hi tcimis,

You are absolutely right with the 2 Job "C" tables. I only have a version 7 PeopleSoft running on DB2 using Australian date format - so writing the code freehand
in notepad without testing. Sorry about that.

I see you did not go with the lazy DEPT_ENTRY_DATE option ;-)

OK, looking at your code - you have converted the dates in your select fields - yet the format has come out MM/DD/YYYY - a bit strange.

The issue you are having with the date formats in the expressions is a 'feature' of query on Oracle and SQL Server. Just substringing the date will give you the same result - you need to pivot it around to
a YYYY-MM-DD format for the max/min to work.

You are almost there - just need to change the oder of your substring:

eg. AND substr(to_char(C.EFFDT,'YYYY-MM-DD'),7,4)||substr( C.EFFDT,1,2)||substr( C.EFFDT,4,2)||TO_CHAR( C.EFFSEQ)

This will get you a 'YYYYMMDD' format. If you are still having issues with the date, try using the substrung date expressions as columns to see the values being returned so that you can
check the date format that is being used in the compares.

 

[tcimis]

Sorry it took me awhile to get back to you but I had to take a break from this to work on something else. I have the dates coming out in the correct format of YYYYMMDD but I am getting confused on which version of the record should be pulled when.

Can you look at what record I'm pulling where and see if you notice something wrong?

I really appreciate your assistance. I thought I had it because one record appeared to be correct but others were wrong.

See the code posted above from Aug 20.

Thanks again for your assistance.