Employee Status as of a specified date 1

[swagaman]

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.

 

[pranalli]

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.

 

[pranalli]

Adding to my above post:

i.e., a selection of:

V_HRP_HSTEMPSTATUS.EFFDT is equal to 12/31/07

 

[pranalli]

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

 

[CharlesCook]

Something like this in your selection critera should get you close [!](ish)[/!].

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

 

[swagaman]

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.

 

[CharlesCook]

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

 

[swagaman]

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

 

[CharlesCook]

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

 

[swagaman]

Thanks - that worked!