I am writing one query to get results based on parent table records and latest child table id based on condition
Sample Data:
*Department*
Id Name
-----------
1 Dept1
2 Dept2
3 Dept3
4 Dept4
*Employee*
Id DeptId quality VerifiedOn
----------------------------------
1 1 Ok '2014-08-01'
2 2 Ok '2014-09-01'
3 2 Best '2014-08-01'
4 2 Good '2014-08-07'
5 4 Good '2014-10-08'
6 4 Ok '2014-10-01'
7 4 Good '2014-09-01'
Requirement
I need to get all department details along with latest good quality employee Id.
Good Quality Employee logic is :
- Take Latest employeeId of Quality is ‘Best’
- If not Best quality is there, take latest of employeeId
- If no employee available for the department return 0 or NULL
[top 1 order by VerifiedOn desc]
Need to create viwew for this
Expected Result:
DeptId DeptName EmpiId
-----------------------
1 Dept1 1
2 Dept2 3
3 Dept3 0
4 Dept4 5
You need to sign in to view this answers
Leave feedback about this