×
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

Employee Status as of a specified date

Employee Status as of a specified date

Employee Status as of a specified date

(OP)
Using ADP with SQLbase -

I need to find the status of employees as of a certain date.  The status is in the V_HRP_HSTEMPSTATUS table.  Thanks to a previous post in this forum, I can find the status with the most recent changedate.  However, for this report I need the last status with a changedate on or before a date specified in a report variable.  Any help would be greatly appreciated.

RE: Employee Status as of a specified date

Sounds like you are running PCPW with HR Perspective, and unfortunately I don't have specific experience with the HRP tables.  However, I can tell you that if it is an effective dated table, you'd simply want to include the effective date field (in Enterprise, it's called "EFFDT") in the report, and then create a selection criteria for your "as of" date.  

RE: Employee Status as of a specified date

Adding to my above post:

i.e., a selection of:

V_HRP_HSTEMPSTATUS.EFFDT is equal to 12/31/07

RE: Employee Status as of a specified date

If the table is not effective dated, then I believe you are out of luck.  

RE: Employee Status as of a specified date

Something like this in your selection critera should get you close (ish).

Quote (swagaman):

Thanks to a previous post in this forum, I can find the status with the most recent changedate.

The key is to add the last bit to what you are using...

V_HRP_HSTEMPSTATUS.EFFDT =
(SELECT
     MAX(INNERALIAS.EFFDT)
FROM
     V_HRP_HSTEMPSTATUS INNERALIAS
WHERE
     ((V_HRP_HSTEMPSTATUS.COMPANYCODE = INNERALIAS.COMPANYCODE)
     AND
     (V_HRP_HSTEMPSTATUS.FILE# = INNERALIAS.FILE#)
     AND
     (V_HRP_HSTEMPSTATUS.EFFDT =< <<REPORT_VARIABLE>>)))

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

RE: Employee Status as of a specified date

(OP)
Thanks for the suggestions.  I am using PCPW with HR Profile and don't think that the tables are effective dated.

I also tried the SQL suggested above.  However, it only gives me records where the date in the most recent record is on or before the report variable date.  So if someone's status is "A" on 11-1-2007 and "T" on 12-1-2007, if I run it for 11-15-2007 I don't get any records for this person.  I only get records for employees whose last change was on or before the variable date.

Any other help or suggestions would be greatly appreciated.

RE: Employee Status as of a specified date

Copy your report to a new report.

Strip out of the report everything you can just keeping the VERY basic necessary tables etc.

post your STRIPED DOWN SQL

I don't want to be confused by a bunch of tables and derived fields and selection critera that have nothing to do with the CORE issue (please).

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

RE: Employee Status as of a specified date

(OP)
Here is the SQL.  Thanks for your assistance.

SELECT
REPORTS.V_HRP_HSTEMPSTATUS.EMPSTATUS,
REPORTS.V_EMPLOYEE.NAME, REPORTS.V_EMPLOYEE.STATUS, REPORTS.V_HRP_HSTEMPSTATUS.CHANGEDATE
FROM
REPORTS.V_EMPLOYEE, REPORTS.V_HRP_HSTEMPSTATUS
WHERE
(((REPORTS.V_HRP_HSTEMPSTATUS.CHANGEDATE = (SELECT
  MAX(INNERALIAS.CHANGEDATE)
FROM
   REPORTS.V_HRP_HSTEMPSTATUS INNERALIAS
WHERE
   INNERALIAS.COMPANYCODE = REPORTS.V_HRP_HSTEMPSTATUS.COMPANYCODE
   AND
   INNERALIAS.FILE# = REPORTS.V_HRP_HSTEMPSTATUS.FILE#
   AND
   REPORTS.V_HRP_HSTEMPSTATUS.CHANGEDATE <=  <<AsOfDate>>
)
)))
AND
(REPORTS.V_EMPLOYEE.COMPANYCODE = REPORTS.V_HRP_HSTEMPSTATUS.COMPANYCODE ) AND (REPORTS.V_EMPLOYEE.FILE# = REPORTS.V_HRP_HSTEMPSTATUS.FILE# )
ORDER BY
REPORTS.V_EMPLOYEE.NAME

RE: Employee Status as of a specified date

A small problem in your SQL:

SELECT
     REPORTS.V_HRP_HSTEMPSTATUS.EMPSTATUS,
     REPORTS.V_EMPLOYEE.NAME,
     REPORTS.V_EMPLOYEE.STATUS,
     REPORTS.V_HRP_HSTEMPSTATUS.CHANGEDATE
FROM
     REPORTS.V_EMPLOYEE, REPORTS.V_HRP_HSTEMPSTATUS
WHERE
     (((REPORTS.V_HRP_HSTEMPSTATUS.CHANGEDATE =
     (SELECT
          MAX(INNERALIAS.CHANGEDATE)
     FROM
          REPORTS.V_HRP_HSTEMPSTATUS INNERALIAS
     WHERE
          INNERALIAS.COMPANYCODE = REPORTS.V_HRP_HSTEMPSTATUS.COMPANYCODE
          AND
          INNERALIAS.FILE# = REPORTS.V_HRP_HSTEMPSTATUS.FILE#
          AND
          INNERALIAS.V_HRP_HSTEMPSTATUS.CHANGEDATE <=  <<AsOfDate>>)
     )))
     AND
     (REPORTS.V_EMPLOYEE.COMPANYCODE = REPORTS.V_HRP_HSTEMPSTATUS.COMPANYCODE )
     AND
     (REPORTS.V_EMPLOYEE.FILE# = REPORTS.V_HRP_HSTEMPSTATUS.FILE# )
ORDER BY
     REPORTS.V_EMPLOYEE.NAME

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

RE: Employee Status as of a specified date

(OP)
Thanks - that worked!

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