[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

ORACLE VIEW - TRAINING NEED ANALYSIS

Hi,
I need assistance regarding training need analysis as follow:
1.      Table HR_ORG_STR_TRAINING – holds information regarding the organizational structure (ORG_ID) and training needed for the position (TRAINING_PLAN_ID)
2.      Table TRAINING_EMPLOYEE - holds information on employee (EMPLOYEE_ID) and the training that employee has completed (TRAINING_ID)
3.      Table HR_EMPLOMENT_HYSTORY holds information for the position of employee (ORGANISATIONAL_STRUCTURE_ID) and the employee ID (EMPLOYEE_ID) and condition POSITION_DATE_TO  is null (current position of employee)
I need view that will show training need analysis according the position that employee is assigned… Approx. 1700 employees!
TX in advance,
Andreja
HR-EMPLOMENT-HYSTORY.TXT
HR-ORG-STR-TRAINING.TXT
TRAINING-EMPLOYEE.TXT
0
andrejaTJ
Asked:
andrejaTJ
  • 2
  • 2
1 Solution
 
johnsoneSenior Oracle DBACommented:
I'm not sure that I got the join fields correct.  You may have to adjust the joins, but this should give you a list of employees, what training plan they are attached to and haven't completed.

SELECT a.employee_id, 
       b.training_plan_id, 
       c.training_id 
FROM   hr_emploment_hystory a 
       join hr_org_str_training b 
         ON a.organisational_structure_id = b.org_id 
       left outer join training_employee c 
                    ON a.employee_id = c.employee_id 
                       AND b.training_plan_id = c.training_id 
WHERE  a.position_date_to IS NULL 
       AND c.training_id IS NULL; 

Open in new window


If this is not correct, please post some sample results that go with your sample data.
0
 
andrejaTJAuthor Commented:
Hi Johnsone,
I got the list as attached...
That means that the listed employee must take the listed training?
I don't understand the results...
TX
Andreja
2014-07-11-164643.jpg
0
 
johnsoneSenior Oracle DBACommented:
Yes, that should be the list of employees and the training they are missing.
0
 
PortletPaulCommented:
best I can come up with is:
select
*
from HR_ORG_STR_TRAINING org
inner join HR_EMPLOMENT_HYSTORY hist on org.org_id = hist.organisational_structure_id
left join TRAINING_EMPLOYEE train on org.training_plan_id = train.training_id
                                 and hist.employee_id = train.employee_id
where hist.position_date_to IS NULL
;

Open in new window

It needs a greater familiarity with the data I think (&/or there are gaps in the small sample)
see http://sqlfiddle.com/#!4/5ff6b/15 if it helps
0
 
PortletPaulCommented:
:) too late anyway.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now