Too much history

[MHurdatADP]

Earlier, I had a thread where I got "no rows found" when trying to display current JOB row and previous JOB row, when no history existed. I found a fix for that: include a condition where the history effdt is either null or equal to the current row.

However, that adds a new problem: Where there is history, I now get two rows of data - one with the current and history data and one with the current data only. What am I missing? Or, how do I get it to display EITHER, not both? I am loading an attachment called "Too many rows.doc" with more details. Thank you for your assistance.

 

[MHurdatADP]

No attachment, I guess. Here are the details of my selection criteria.

Tables are JOB and JOB1, where JOB.EMPLID is linked to JOB1.EMPLID

1. data field JOB.EFFDT is equal to formula (SELECT MAX(JOB.EFFDT) FROM PS_JOB
WHERE JOB.EMPLID = JOB.EMPLID)

7. ANY of the following apply:
7.1 data field JOB1.EFFDT is equal to data field JOB.EFFDT.

This will display the current row if there is no history. Unfortunately, it also displays if there IS history.

7.2 data field JOB1.EFFDT is equal to formula (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = JOB.EMPLID AND A.EFFDT < JOB.EFFDT)

This displays the “true” history row.


How do I write it so EITHER one or the other is true, so I only get one row, instead of two?

 

[CharlesCook]

I would need to see ALL of the SQL to sort it out.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

Like this?

SELECT DISTINCT
(select distinct decode(b.action,'HIR','No Reason Given',b.descr)
from ps_actn_reason_tbl b
where (b.action_reason = job1.action_reason and
b.action = job1.action and
job1.action_reason <> ' ') or
(b.action_reason = 'TMP' and
b.action = 'HIR' and
job1.action_reason = ' ')), "JOB1"."CHANGE_PCT",
"JOB"."ACTION", "JOB"."AL_PAY_FREQUENCY", "JOB"."ANNUAL_RT", "JOB"."CHANGE_AMT", "JOB"."CHANGE_PCT", "JOB"."EFFDT", "JOB"."EMPLID", "JOB"."FLSA_STATUS", "JOB"."JOBCODE", "JOB"."LOCATION", "JOB"."HOME_DEPARTMENT", "JOB"."GRADE", "JOB"."STEP", "JOB"."RPTC_ALT_SHF_DIF", "JOB"."PAYGROUP", "PS_RPTC_EMP_ECG_VW"."RPTC_ECG_GRADE", "PS_EMPLOYMENT"."FLAG_1", "JOB1"."EFFDT", "PS_PERSONAL_DATA"."BIRTHDATE", "PS_PERSONAL_DATA"."NAME", "PS_PERSONAL_DATA"."ORIG_HIRE_DT", "PS_ACTN_REASON_TBL"."ACTION_REASON", "PS_ACTN_REASON_TBL"."DESCR", "PS_ACTN_REASON_TBL"."EFFDT", "PS_JOBCODE_TBL"."JOBCODE", "PS_JOBCODE_TBL"."DESCR", "PS_JOBCODE_TBL"."EFFDT", "XLATTABLE"."FIELDNAME", "XLATTABLE"."FIELDVALUE", "XLATTABLE"."EFFDT", "XLATTABLE"."EFF_STATUS", "XLATTABLE"."XLATSHORTNAME", "XLATTABLE"."LANGUAGE_CD"
FROM
"PS_JOB" "JOB", "PS_RPTC_EMP_ECG_VW", "PS_EMPLOYMENT", "PS_JOB" "JOB1", "PS_PERSONAL_DATA", "PS_ACTN_REASON_TBL", "PS_JOBCODE_TBL", "XLATTABLE"
WHERE
((("JOB"."EFFDT" = (SELECT MAX(JOB.EFFDT) FROM PS_JOB
WHERE JOB.EMPLID = JOB.EMPLID)) AND
("JOB"."COMPANY" = 'WAH') AND
("JOB"."EMPL_TYPE" = 'S') AND
("JOB"."EMPL_STATUS" = 'A') AND
("JOB"."EMPL_RCD_NBR" = 0) AND
("JOB"."EMPLID" = '0002356') AND
(("JOB1"."EFFDT" = "JOB"."EFFDT") OR
("JOB1"."EFFDT" = (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = JOB.EMPLID
AND A.EFFDT < JOB.EFFDT))) AND
("JOB"."REG_TEMP" = 'R') AND
(("XLATTABLE"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "XLATTABLE" INNERALIAS
WHERE "INNERALIAS"."FIELDNAME" = "XLATTABLE"."FIELDNAME"
AND "INNERALIAS"."FIELDVALUE" = "XLATTABLE"."FIELDVALUE"
AND "INNERALIAS"."EFF_STATUS" = "XLATTABLE"."EFF_STATUS"
AND "INNERALIAS"."LANGUAGE_CD" = "XLATTABLE"."LANGUAGE_CD"
AND "INNERALIAS"."EFFDT" <= SYSDATE))) AND
("XLATTABLE"."FIELDNAME" = 'AL_PAY_FREQUENCY') AND
("XLATTABLE"."EFF_STATUS" = 'A') AND
("XLATTABLE"."LANGUAGE_CD" = 'ENG') AND
(("PS_RPTC_EMP_ECG_VW"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_RPTC_EMP_ECG_VW" INNERALIAS
WHERE "INNERALIAS"."EFFSEQ" = "PS_RPTC_EMP_ECG_VW"."EFFSEQ"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_RPTC_EMP_ECG_VW"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "PS_RPTC_EMP_ECG_VW"."EMPLID"
AND "INNERALIAS"."EFFDT" = "PS_RPTC_EMP_ECG_VW"."EFFDT")
AND
"PS_RPTC_EMP_ECG_VW"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_RPTC_EMP_ECG_VW" INNERALIAS
WHERE "INNERALIAS"."EFFSEQ" = "PS_RPTC_EMP_ECG_VW"."EFFSEQ"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_RPTC_EMP_ECG_VW"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "PS_RPTC_EMP_ECG_VW"."EMPLID"
AND "INNERALIAS"."EFFDT" <= SYSDATE))) AND
(("PS_ACTN_REASON_TBL"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_ACTN_REASON_TBL" INNERALIAS
WHERE "INNERALIAS"."ACTION_REASON" = "PS_ACTN_REASON_TBL"."ACTION_REASON"
AND "INNERALIAS"."DESCR" = "PS_ACTN_REASON_TBL"."DESCR"
AND "INNERALIAS"."EFFDT" <= SYSDATE))) AND
(("PS_JOBCODE_TBL"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOBCODE_TBL" INNERALIAS
WHERE "INNERALIAS"."JOBCODE" = "PS_JOBCODE_TBL"."JOBCODE"
AND "INNERALIAS"."DESCR" = "PS_JOBCODE_TBL"."DESCR"
AND "INNERALIAS"."EFFDT" <= SYSDATE)))))
AND
("JOB"."EMPLID" = "PS_RPTC_EMP_ECG_VW"."EMPLID" ) AND ("JOB"."SAL_ADMIN_PLAN" = "PS_RPTC_EMP_ECG_VW"."SAL_ADMIN_PLAN" ) AND ("JOB"."GRADE" = "PS_RPTC_EMP_ECG_VW"."GRADE" ) AND ("JOB"."EMPL_RCD_NBR" = "PS_RPTC_EMP_ECG_VW"."EMPL_RCD_NBR" ) AND ("JOB"."EFFSEQ" = "PS_RPTC_EMP_ECG_VW"."EFFSEQ" ) AND ("JOB"."EFFDT" = "PS_RPTC_EMP_ECG_VW"."EFFDT" ) AND ("JOB"."EMPLID" = "PS_EMPLOYMENT"."EMPLID" ) AND ("JOB"."EMPLID" = "JOB1"."EMPLID" ) AND ("JOB1"."ACTION_REASON" = "PS_ACTN_REASON_TBL"."ACTION_REASON"(+) ) AND ("JOB"."EMPLID" = "PS_PERSONAL_DATA"."EMPLID" ) AND ("JOB"."JOBCODE" = "PS_JOBCODE_TBL"."JOBCODE" ) AND ("JOB"."AL_PAY_FREQUENCY" = "XLATTABLE"."FIELDVALUE" )
ORDER BY
"JOB"."PAYGROUP", "PS_PERSONAL_DATA"."NAME

 

[CharlesCook]

yep


CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

I guess you haven't had a chance to look at the sql?

 

[CharlesCook]

Hi sorry it has taken me so long to get back to you.
I decided to try to walk you through the way I do this instead of working
through your SQL. I think if we work on this together it will work out ok.


Start a new report

Add the Personal_Data table
Make Name "Include in report"
Make EMPLID "Include only for use in query"

Add The Job Table
Make EMPLID "Include only for use in query"
Make EFFDT "Include in report"

USE MAX Effective Dating

Link using Personal_Data EMPLID = Job EMPLID

Add another Job Table
Make EMPLID "Include only for use in query"
Make EFFDT "Include in report"

USE MAX Effective Dating

Link using Personal_Data EMPLID = Job2 EMPLID
INCLUDE UNMATCHED RECORDS on the Personal_Data SIDE


Click on the Selections Button at the top of the screen.

First we need to deal with people that have no history. We are going to tell the SQL that Job2 will either be Effective Dated or Empty.

Click on the numbered box in front of the Job2 Effective Dating line (this should be line 2)
Select "Create a new list, and move this item into it" (It should now be line 2.1)
Change "all of the following apply" to "any of the following apply" (This is on line 2)
Click on line 2.1 and select "Insert new selection criteria after this item"
Make line 2.2 read data field Job2.EMPLID is null

OK now we need to tweak the Effective Dating for Job2 so it's EFFDT is always less then the first Job table we added.

Click on line 2.1 and select "Convert this item to SQL"
The selection is now in SQL syntax and is underlined completely.
Click on the SQL syntax selection. (A new window will pop up)
COPY THE SQL FROM THE NEW WINDOW AND POST IT HERE.
I WILL POST BACK THE CHANGES.

Make the changes I post and run the report. Then let me know how it goes.




CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

MHurdatADP you ever get a chance to look at this?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

Charles,
Yes, I looked at it. Sorry I haven't responded. Very busy. I reviewed it and it looked exactly like what I was trying to do. However, because of your message, I reviewed it again.The only difference I see is that I'm using effdt instead of emplid as the test for null. I will change my criteria to look at emplid, instead of effdt and let you know. Thanks.

 

[MHurdatADP]

Charles,
Now I remember why I didn't finish checking this. From your ealier reply:

"COPY THE SQL FROM THE NEW WINDOW AND POST IT HERE.
I WILL POST BACK THE CHANGES.

Make the changes I post and run the report. Then let me know how it goes."

I was waiting for more data from you.

 

[CharlesCook]

Sorry I may not have been clear on this step:

Click on line 2.1 and select "Convert this item to SQL"
The selection is now in SQL syntax and is underlined completely.
Click on the SQL syntax selection. (A new window will pop up)
COPY THE SQL FROM THE NEW WINDOW AND POST IT HERE.
I WILL POST BACK THE CHANGES.

In this step I only want to see the SQL from the Selection Critera item you converted to SQL. It will make it easer to focus.

P.S. Do you work for ADP? What location? I have done lots of ReportSmith work for them.



CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

Yes, ADP. I retired from the Army 7 years ago, becames asst controller at a large university and hated it. Got into consulting with AG Consulting, which was owned by ADP. Obsorbed into straight ADP after AG was sold. I am an implementer in Professional Services and took it upon myself to learn ReportSmith. I must say I'm getting pretty good at it. However, there are a few things which still drive me crazy. This is one of them.

The links and selections you are talking about here are very similar to what I already have. My problem is that if I add JOB2 EMPLID IS NULL (I was using effdt is null), my no-history people disappear. When I change it to JOB2 EFFDT = JOB.EFFDT, the no-history people appear, but now I have extra rows for those with history.

Notes: You say "add job and use MAX effective dating". When I add an effective dated table, I have been using the default radio button "As of current date". Is that what I should use? Or should I use "latest". Second: The effective dating box is disabled for the second Job table.

Here's the SQL:

("JOB2"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EFFSEQ" = "JOB2"."EFFSEQ"
AND "INNERALIAS"."EMPL_RCD_NBR" = "JOB2"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB2"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB2"."EFFDT")
AND
"JOB2"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EFFSEQ" = "JOB2"."EFFSEQ"
AND "INNERALIAS"."EMPL_RCD_NBR" = "JOB2"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB2"."EMPLID"
AND "INNERALIAS"."EFFDT" <= SYSDATE))

 

[CharlesCook]

("JOB2"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EFFSEQ" = "JOB2"."EFFSEQ"
AND "INNERALIAS"."EMPL_RCD_NBR" = "JOB2"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB2"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB2"."EFFDT")
AND
"JOB2"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EFFSEQ" = "JOB2"."EFFSEQ"
AND "INNERALIAS"."EMPL_RCD_NBR" = "JOB2"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB2"."EMPLID"
AND "INNERALIAS"."EFFDT" < "PS_JOB"."EFFDT"))

We want to make this less then the first job table's EFFDT.

As of current date is the way to go. This will keep you away from the stuff that will happen in the future.

The effective dating box is disabled for the second Job table. Did you click on the table first?

Check out the ReportSmith button @ CharlesCook.com for more info on me. My web site is being rebuilt but this section is working.



CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

OK. My selection criteria look very much like yours. I did have to change "PS_JOB" to 'JOB1", since I'm using an alias for PS_JOB.
This selection brings up everyone with a current and history row - only one row. That's good. However, the NULL is not working, as is isn't working for me. I get NO HIRs or DBL/DBL only rows. My total count is 164 rows, where I should bet getting around 2,400.

 

[CharlesCook]

screen print the Tables Screen & Selections Screen and email it to me.

You can find my email address at charlescook.com

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

I am sending you a Word doc with even more screen prints than you ask for.