Home » Other » Training & Certification » grouping the records problem help please
grouping the records problem help please [message #317640] Fri, 02 May 2008 04:57 Go to next message
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
Re: grouping the records problem help please [message #317643 is a reply to message #317640] Fri, 02 May 2008 05:05 Go to previous message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Expert notion is defined in the sticky: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel
Previous Topic: employee supervisor relationship help please
Next Topic: UPDATE sql statement help please
Goto Forum:
  


Current Time: Tue Oct 21 08:44:41 CDT 2014

Total time taken to generate the page: 0.76973 seconds