×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

pranalli - Viewing most current record only (LOOK HERE) !!!!!

pranalli - Viewing most current record only (LOOK HERE) !!!!!

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

Charles,

Thank you for your response, however that route doesn't quite work, let me explain.

I need to see the last action which was a "PAY" action type or "MER or HIR" action reason.  If I effective date the table, it will show me only the people whose LAST action were one of those, but exclude them if they had some sort of intermittent action like a data change.  

What I really need is to see all of my employees and the last time they received either a "PAY" or "HIR" action, regardless of what the most recent action may have been if not a PAY or HIR.

Right now, the report is working great with the exception of the fact that I am getting that historical data.  I had taken out the effdt of the Job table so that I could view all of the PAY and HIR actions for each employee.  Now I need to figure out how to tell Reportsmith to only show me the most recent action date out of this pool of information without excluding those individuals whose last action may not have been a PAY or HIR (for example, may we had to fix their next review date, so they would have a DTA action).

I'm truly at a loss on how I can do this.  I thought maybe I could set up a selection criteria that says:

PS.JOB.ACTION_DT is equal to formula SELECT MAX(PS.JOB.ACTION_DT) FROM PRANALLI.PS.JOB

(I know the syntax isn't exact there, I'm just showing the logic).  But when I do that I just get errors saying like missing expression.  

Your collaboration is greatly appreciated Charles, and of course anyone else too!

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

In my previous post, ignore the part where I need to see only "MER" or "HIR" action reason -- that is incorrect.  Stick to "PAY" or "HIR" action as those encompass all of the various "Action Reasons" that I need.

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

(OP)
Keep the Selection Critera for Effective Dating the Job table.   
Click on this selection and convert it to SQL.  
Click on the SQL Selection Critera to get into the SQL editor.
Add to the second part of the SQL AND "PS_JOB"."ACTION" IN ('HIR','PAY')

The SQL Selection for the Effective Dating of JOB will look like this:

("PS_JOB"."EFFSEQ"= (
    SELECT MAX("INNERALIAS"."EFFSEQ")
    FROM "PS_JOB" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
    AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EFFDT" = "PS_JOB"."EFFDT")
AND
"PS_JOB"."EFFDT" = (
    SELECT MAX("INNERALIAS"."EFFDT")
    FROM "PS_JOB" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
    AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EFFDT" <= SYSDATE)
    AND "PS_JOB"."ACTION" IN ('HIR','PAY'))

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

I'll give it a shot when I have a few minutes and let you know how it goes.  Thanks!

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

I verified the SQL line for line, that seems to cause the exact same problem.  It excludes employees whose last action was not either "PAY" or "HIR"  

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

(OP)
My Bad....

("PS_JOB"."EFFSEQ"= (
    SELECT MAX("INNERALIAS"."EFFSEQ")
    FROM "PS_JOB" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
    AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EFFDT" = "PS_JOB"."EFFDT")
AND
"PS_JOB"."EFFDT" = (
    SELECT MAX("INNERALIAS"."EFFDT")
    FROM "PS_JOB" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
    AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_JOB"."EMPL_RCD_NBR"
    AND "INNERALIAS"."EFFDT" <= SYSDATE)
    AND "INNERALIAS"."ACTION" IN ('HIR','PAY'))

You will lose people without a PAY or HIR.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

Wow, that worked!  I still have one or two duplicates, but that's because of poor data entry on our payroll side -- I can work with that easily.  

I don't know if it is outside the scope of what can be explained here, but I'd really like to understand why that worked, but the other method didn't?  As far as I can tell, the only difference is that we used an alias instead of the table name?  

Also, just FYI, I had to move the right parenthesis next to 'SYSDATE' to after the new line to get it to work right.  

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

(OP)
I can't really explain it very well in a text comment.  But it has to do with looking at the inner query (INNERALIAS) and the outer query (PS_JOB).  The inner query happens for each record selected by the outer query.  So using the INNERALIAS alias kept everything in the context of the sub query.

Anyway it works this way...

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

I get what you're saying.  

Anyway, thanks a ton for your help it's greatly appreciated.  I just moved into this position, and the previous person was running a report with ALL historical data and then running it through a query in Access to weed out data.  Needless to say, it was an inefficient use of the ADP system, and quite prone to error.  

My goal was to rid the need for all of that, and I thank you for your help in getting there!  

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

(OP)
Your welcome... Check out my website if you get a chance.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

Pranalli -
I believe I have a solution for you.

I am assuming you have an Oracle database.

First, remove the effective date line from your Report Query - Selections.  Next, insert new SQL criteria.  Copy the following:

CODE

TO_CHAR(J.EFFDT, 'YYYYMMDD') || J.EFFSEQ = (
SELECT MAX(TO_CHAR(iJ.EFFDT, 'YYYYMMDD') || iJ.EFFSEQ)
FROM PS_JOB iJ
WHERE J.EMPLID = iJ.EMPLID
  AND J.EMPL_RCD_NBR = iJ.EMPL_RCD_NBR
  AND ((J.ACTION = 'HIR') OR
       (J.ACTION = 'PAY' AND J.ACTION_REASON = 'MER'))
  AND J.EFFDT <= SYSDATE)

TO_CHAR(J.EFFDT, 'YYYYMMDD') || J.EFFSEQ alters the date and concatenates the Effseq into a numerical string such that you get the true max row.  I also included the Action and Action_Reason criteria.  ‘MER’ is typically associated with the Action of ‘Pay’.  This is indicated thru the use of () as well as the OR and AND operators.

I hope this helps.  Feel free to contact me if you have any other questions.

RSGeek
rsgeek@wi.rr.com

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

Thank you both for your help, it was great learning experience for me.  As it turns out though, the report was still dropping some records.  

I ended up getting the report within arms length of working perfectly, but ultimately scratched the project for now.  I had it showing last increase amount along with the max row of the review date table, but it was still dropping approximately 100 records, and I was unable to determine why.  I got ADP on the phone and on my computer to look at it with me, but even they said that this report is so complex they'd have to spend hours working on it and charge me -- but forget that, I like figuring this stuff out myself!  

Anyway, what I did for now is create two separate reports.  I removed the "last increase amount" and "last increase percentage" fields from my monthly review report (these were the fields that were causing the drop in records), and created it as its own report.  I then export them both into Excel and simply use a vlookup to merge the last amount data into my review date report.  At least this way I am 100% confident that I am getting all of my records and reliable data.  

But bottom line if I am to revisit this issue, I need to somehow create a report where I can bring in the inc_amt and  inc_pct fields associated with the most recent actions PAY or PRO (and in some cases, XFR).  I tried including "HIR" as one of the actions since I assumed everybody would at least have a "HIR" action, but that didn't help.  

RE: pranalli - Viewing most current record only (LOOK HERE) !!!!!

Correction to my last paragraph:

But bottom line if I am to revisit this issue, I need to somehow create a report where I can bring in the inc_amt and  inc_pct fields associated with the most recent actions PAY or PRO (and in some cases, XFR) but still keep ALL records (meaning every employee should show up on the report regardless of if they've received an increase).  I know how to do this by itself, but combined with the 6+ other tables that this report has, it causes problems.  I tried including "HIR" as one of the actions since I assumed everybody would at least have a "HIR" action, but that didn't help.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close