CODEWITHSUNDEEP
sqloracle-database
Khanjan
Khanjan

Reputation: 33

Get The Employee who has taken Minimum days of leave

Recently in one of the interviews I had given , I was asked question to

write a query to find the employee with min days of leave in previous 3 months department wise.

The table structure was 

EMPID | TO_DATE | FROM_DATE | LEAVE _TYPE | DEPT_NO

I was able to write the query 

SELECT 
min(days) FROM (SELECT id ,(sum((TO_DATE-FROM_DATE)+1) ) days ,dept  
FROM emp_leave  
WHERE to_date  between  ADD_MONTHS(sysdate,-3)  AND sysdate group by id,dept) 
group by dept  ;

but when I try to select the emp_id I have to add it in group by statement.

I was stuck there.

Upvotes: 3

Views: 159

Answers (2)

Steven
Steven

Reputation: 15318

I think the query should have been something like 

 select dept, min(id) keep (dense_rank last order by days)
from (  SELECT  id ,
            (sum((TO_DATE-FROM_DATE)+1) ) days ,
            dept 
    FROM emp_leave 
    WHERE to_date between ADD_MONTHS(sysdate,-3) 
    AND sysdate group by id,dept) 
group by dept
;

Well of course, in SQL you have a lot of different way to do this, but when it is about ranking stuff, the first/last function is very useful

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Try this

 SELECT id
    ,(sum((TO_DATE - FROM_DATE) + 1)) days
    ,dept
FROM emp_leave
WHERE to_date BETWEEN ADD_MONTHS(sysdate, - 3)
        AND sysdate
GROUP BY id
    ,dept
ORDER BY days LIMIT 1

Upvotes: 1

Related Questions