grouping the records problem help please [message #317640] |
Fri, 02 May 2008 04:57 |
sunildatt_b
Messages: 3 Registered: May 2008
|
Junior Member |
|
|
question is
===============
The company’s human resources department needs to report to upper management the current skill levels of all employees. They need a query that shows, for each employee, which skills they have completed training for, the number of times they received training for that skill and the most recent date of those trainings. This is important because some trainings must be renewed on a set cycle. The output should also include employees who have received no training.
i developed a query as follows
QUERY
============
SELECT
E.EMP_NUM EMPLOYEE,
T.S_CODE SKILLCODE,
S.S_DESCRIPTION SKILLDESC,
COUNT(S.S_CODE),
T.DATE_COMPLETED MOSTRECENTCOMPLETEDATE
FROM
EMPLOYEE E,
SKILL S,
TRAINING T
WHERE E.EMP_NUM=T.EMP_NUM
AND S.S_CODE=T.S_CODE
GROUP BY
T.S_CODE,
S.S_DESCRIPTION ,
E.EMP_NUM,
T.DATE_COMPLETED
ORDER BY T.DATE_COMPLETED DESC
OUTPUT IS THAT I GOT IS
===========
EMPLOYEE SKILLCODE SKILLDESC COUNT MRCD
==============================================
E1111 S001 VISUALBASIC 1 1/16/2008
E1112 S003 ORACLE APPLICATIONS 1 12/12/2007
E1111 S001 VISUALBASIC 2 1/17/2007
E1113 S002 JAVA J2EE 1 1/16/2006
BUT THE OUTPUT SHOULD LOOK LIKE
OUTPUT IS THAT I GOT IS
===========
EMPLOYEE SKILLCODE SKILLDESC COUNT MRCD
==============================================
E1111 S001 VISUALBASIC 2 1/16/2008
E1112 S003 ORACLE APPLICATIONS 1 12/12/2007
E1113 S002 JAVA J2EE 1 1/16/2006
MRCD means most recent date completed
can any one help me regarding this this is an assignment for me i need to submit it by tommorrow
|
|
|
|