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
andrejaTJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
:) too late anyway.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.