I have a table that is used to track staff training. This table has 3 fields:
EMP_ID (Staff ID number)
docType (identifies the type of training, will be a number from 1-4)
docDate (identifies the date that training was given)
Staff can be trained several times on the same training type. TrainingType 1 I need the two most recent training dates and the other 3, I only need the most recent. However, If I get the most recent 2 dates of all 4 types, I can filter the information in my report. So I need the information in one of two ways. The preferred way would be to return one record for each employee with a date for trainingTyp1-1 (the first training), trainingType1-2(the second training),trainingType2, trainingType3, trainingType4.
I have no clue how to get this...Maybe one of the gurus has an idea.
The other method would be to reduce the data down to 2 entries for each employee for trainingType1 (the two most recent dates) and one entry for each of the other training types (two entries would be acceptable as well)
So if this is the table Data:
The preferred method would result in:
The alternate method would return:
I came close but it only lists the top two dates for one type of training not for each training. This is what I used:
Any ideas?
EMP_ID (Staff ID number)
docType (identifies the type of training, will be a number from 1-4)
docDate (identifies the date that training was given)
Staff can be trained several times on the same training type. TrainingType 1 I need the two most recent training dates and the other 3, I only need the most recent. However, If I get the most recent 2 dates of all 4 types, I can filter the information in my report. So I need the information in one of two ways. The preferred way would be to return one record for each employee with a date for trainingTyp1-1 (the first training), trainingType1-2(the second training),trainingType2, trainingType3, trainingType4.
I have no clue how to get this...Maybe one of the gurus has an idea.
The other method would be to reduce the data down to 2 entries for each employee for trainingType1 (the two most recent dates) and one entry for each of the other training types (two entries would be acceptable as well)
So if this is the table Data:
Code:
EMP_ID docType docDate
2541 1 5/2/2012
1038 4 3/25/2012
2541 1 4/18/2012
1892 1 3/10/2012
2541 3 3/7/2012
1038 4 5/15/2012
2541 1 6/25/2012
Code:
EMP_ID trn1of1 trn2of1 trn2 trn3 trn4
2541 5/2/2012 6/25/2012 3/7/2012
1038 5/15/2012
1892 3/10/2012
Code:
EMP_ID docType docDate
2541 1 5/2/2012
2541 1 6/25/2012
2541 3 3/7/2012
1038 4 5/15/2012
1892 1 3/10/2012I came close but it only lists the top two dates for one type of training not for each training. This is what I used:
Code:
SELECT *
FROM tblDocsIssued AS T2
WHERE docDate in
(SELECT TOP 2 docDate
FROM tblDocsIssued
where tblDocsIssued.EMP_ID = T2.EMP_ID
ORDER BY docDate DESC);
