Issue with selecting max date on inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with selecting max date on inner join

Author

Topic

RalphWiggum
Starting Member

13 Posts

Posted - 2011-04-14 : 10:31:53

I have 4 tables:
1 -- HRPERSNL (all P_XXXX field names)
2 -- HJOBHIS (contains emp job history and dates of job changes)
3 -- HEVENTS (contains notes on employees)
4 -- HRTABLES (a table containing value codes for different fields)

I am trying to return a list of all employees with information from all 4 tables, but I only want information as it pertains to their current job. Unfortunately, information on their jobs are stored in HJOBHIS and any active employee can have multiple records in that table if they have ever changed jobs within the organization.

I have the following stored proc in an attempt to return information about each employee and their current job and insert it into a table (IndustrySafeExport):

INSERT INTO IndustrySafeExport(E_EMPLOYEE_ID, E_FIRST_NAME, E_INITIAL, E_LAST_NAME, E_FACILITY_FK, E_HIRE_DATE, E_DOB, E_GENDER, E_ADDRESS, E_ADDRESS2, E_CITY, E_STATE, E_ZIP, E_COUNTRY, E_MARITAL_STATUS, E_PHONE, E_WORK_PHONE, E_JOB_TITLE, E_JOBCLASS, E_EMAIL, E_ARCHIVE_STATUS, E_CDL_EXP_DATE, E_CDL_CLASS, E_CDL_MED_EXAM_DATE, E_CDL_MED_EXAM_RENEW_DATE, E_WORKER_TYPE, E_SUPERVISOR_ID, E_SSNO, E_DEPARTMENT, E_CDL_NUMBER, E_HOURLY_WAGE, E_CHADATE)

SELECT DISTINCT
P_EMPNO AS E_EMPLOYEE_ID,
P_FNAME AS E_FIRST_NAME,
P_MI AS E_INITIAL,
P_LNAME AS E_LAST_NAME,
HRT.[DESC] AS E_FACILITY_FK,
P_ORIGHIRE AS E_HIRE_DATE,
P_BIRTH AS E_DOB,
P_SEX AS E_GENDER,
P_HSTREET1 AS E_ADDRESS,
P_HSTREET2 AS E_ADDRESS2,
P_HCITY AS E_CITY,
P_HSTATE AS E_STATE,
P_HZIP AS E_ZIP,
P_HCOUNTRY AS E_COUNTRY,
P_MARRIED AS E_MARITAL_STATUS,
P_HPHONE AS E_PHONE,
p_BUSPHONE AS E_WORK_PHONE,
P_JOBTITLE AS E_JOB_TITLE,
P_JOBCODE AS E_JOBCLASS,
P_EMPEMAIL AS E_EMAIL,
P_ACTIVE AS E_ARCHIVE_STATUS,
E_NEXTDATE AS E_CDL_EXP_DATE,
NULL AS E_CDL_CLASS,
NULL AS E_CDL_MED_EXAM_DATE,
NULL AS E_CDL_MED_EXAM_RENEW_DATE,
NULL AS E_WORKER_TYPE,
P_SUPERNO AS E_SUPERVISOR_ID,
NULL AS E_SSNO,
NULL AS E_DEPARTMENT,
E_COMMENTS AS E_CDL_NUMBER,
NULL AS E_HOURLY_WAGE,
HJOBHIS.J_CHADATE AS E_CHADATE

FROM HRPERSNL
INNER JOIN (SELECT MAX(J_CHADATE) AS E_CHADATE FROM HJOBHIS) HJOBHIS ON HJOBHIS.J_EMPNO = HRPERSNL.P_EMPNO
INNER JOIN HRTABLES AS HRT ON HRT.CODE = HJOBHIS.J_LEVEL2
LEFT OUTER JOIN HEVENTS ON HRPERSNL.P_EMPNO = HEVENTS.E_EMPNO

WHERE HRT.[DESC] IN ('Multiple','Values','Listed','Here')

I'm getting the following errors:

Msg 207, Level 16, State 1, Procedure zz_Generate_IndustrySafe_Employee_Import_File, Line 52
Invalid column name 'J_EMPNO'.
Msg 207, Level 16, State 1, Procedure zz_Generate_IndustrySafe_Employee_Import_File, Line 53
Invalid column name 'J_LEVEL2'.
Msg 207, Level 16, State 1, Procedure zz_Generate_IndustrySafe_Employee_Import_File, Line 48
Invalid column name 'J_CHADATE'.

Am I jacking up MAX() or should I be using something else? Gah!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-14 : 10:55:01

FROM HRPERSNL
INNER JOIN
(SELECT J_EMPNO,J_LEVEL2,J_CHADATE,row_number() over(Partition by J_EMPNO order by J_CHADATE desc) AS JDateRank
FROM HJOBHIS
) HJOBHIS ON
HRPERSNL.P_EMPNO = HJOBHIS.J_EMPNO
INNER JOIN HRTABLES AS HRT ON
HJOBHIS.J_LEVEL2 = HRT.CODE
LEFT OUTER JOIN HEVENTS ON
HRPERSNL.P_EMPNO = HEVENTS.E_EMPNO

WHERE HJOBHIS.JDateRank = 1
and HRT.[DESC] IN ('Multiple','Values','Listed','Here')

Jim

Everyday I learn something that somebody else already knew

RalphWiggum
Starting Member

13 Posts

Posted - 2011-04-14 : 11:14:42

This will work :)
Unfortunately, the administration controls that are in place for HR data entry need "reprogrammed", as I'm getting dupes for some reason...but not too many.
Thanks, jimf!! I'm not familiar with over()

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-14 : 11:25:18

You're Welcome. If you're getting dups it may be because there is more than one CHADATE assigned to J_EMPNO and you may need to add something to the over clause (either the partition or the order by) or you may just be missing something somewhere.
Happy hunting!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources