Get EE Supervisor's EEID in straight SQL

Hi TimC,

This one is pretty straightforward:

[code] select wa.employee ,sup_wa.employee as sup_employee from prodline.workassignment as wa inner join prodline.workassignment as sup_wa on sup_wa.hrorganization = wa.hrorganization and sup_wa.assignmentissupervisor = wa.directsupervisor and sup_wa.active = 1 and hex(sup_wa.deleteflag) = repeat('0',32) where wa.hrorganization = 'whatever' and wa.active = 1 and hex(wa.deleteflag) = repeat('0',32) [/code] 

Note that this is a one-to-many relationship - there may be multiple employees in the Supervisor role (if your company allows this).

Good Luck!
Kelly

Nice. Thanks Woozy!!

I'm a late comer to this post... Woozy's SQL is spot on. It is exactly what I have been using - something I keep in my back pocket as it is a frequent request I get.
Best Wishes to you Tim!!!

Tammy

Hello Woozy,
You're right. Returns an EE for each supervisor. Not what our output wants. Just any 1 supervisor works for us.
So, this T-SQL works.
SELECT a.*,
SUPEEID = (SELECT TOP 1 EMPLOYEE
FROM LTMTRAIN.WORKASSIGNMENT
WHERE HRORGANIZATION = a.HRORGANIZATION
AND ASSIGNMENTISSUPERVISOR = a.DIRECTSUPERVISOR
)
FROM(
SELECT LTMTRAIN.JOB.SHORTDESCRIPTION,
LTMTRAIN.USERFIELD_STORAGE.ALPHA AS DIVISION,
w.DIRECTSUPERVISOR,
w.ASSIGNMENTISSUPERVISOR,
LTMTRAIN.EMPLOYEE.HRORGANIZATION
FROM LTMTRAIN.WORKASSIGNMENT w
INNER JOIN LTMTRAIN.JOB ON w.HRORGANIZATION = LTMTRAIN.JOB.HRORGANIZATION
AND w.JOB = LTMTRAIN.JOB.JOB
INNER JOIN LTMTRAIN.HRORGANIZATIONUNIT ON w.HRORGANIZATION = LTMTRAIN.HRORGANIZATIONUNIT.HRORGANIZATION
AND w.HRORGANIZATIONUNIT = LTMTRAIN.HRORGANIZATIONUNIT.HRORGANIZATIONUNIT
INNER JOIN LTMTRAIN.EMPLOYEE ON w.HRORGANIZATION = LTMTRAIN.EMPLOYEE.HRORGANIZATION
AND w.EMPLOYEE = LTMTRAIN.EMPLOYEE.EMPLOYEE
AND w.WORKASSIGNMENT = LTMTRAIN.EMPLOYEE.PRIMARYWORKASSIGNMENT
LEFT OUTER JOIN LTMTRAIN.USERFIELD_STORAGE ON LTMTRAIN.HRORGANIZATIONUNIT.UNIQUEID = LTMTRAIN.USERFIELD_STORAGE.UFSUNIQUEID
AND LTMTRAIN.USERFIELD_STORAGE.UFSFIELDNAME = 'ADPHRDept'
AND LTMTRAIN.USERFIELD_STORAGE.UFSCLASSNAME = 'HROrganizationUnit'
) AS a

Hi Tim,

I'm glad that works for you.

Just an observation - I noticed that your query doesn't include any statements to exclude deleted records - on DB2 this is done by "where hex(deleteflag) = repeat('0',32)". It is important to note that Landmark doesn't really "delete" anything - it only marks records as deleted using the "deleteflag" field. So, if a record was "deleted" from employee, work assignment, supervisor, etc, they will be returned by your query if you don't exclude them. This kicked our butts for a while until we figured it out.

By the way, if you happen to be interfacing to S3, I believe the interface uses the lowest-valued supervisor employeeID to pass to the S3 Supervisor table. You may want to reflect that in your query if you want this output to match S3. This may have changed in later versions of TM.

Kelly

Good points Kelly,

Also, for connecting/interfacing with S3, if you have multiple companies, you will need a connection of LTM - HRORGANIZATION = SE - COMPANY. Note when trying to make this connection, COMPANY in S3 is a number field and HRORGANIZATION in LTM is actually a char/string field. I typically use cast(businessclass.HRORGANIZATION as numeric) in order to complete the link.

Hi FireGeek21,

That's a good point. There is actually a cross-reference (PFIXrefCode/PFIXrefValue) that maps TM HROrganization/HROrganizationUnit to S3 Company/ProcessLevel/Department. This is what the S3 interface flow uses for this mapping:
Xref Code: ProcessLevelDepartment
Source: LTM
Destination: HRM

Kelly

Thanks all. The REPEAT() function doesn't exist in SQL Server. There is a REPLICATE() with the same args. I did add that to the final query. I just wanted to make sure I had the right table/join operation.

Thanks all!