Clever Geek Handbook

Get an employee who took minimum vacation days

Recently, in an interview I gave, I was asked the question

write a query to find an employee with minimum vacation days in the previous 3 month department.

The table structure was

EMPID | TO_DATE | FROM_DATE | LEAVE _TYPE | DEPT_NO

I managed to write a request

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 emp_id

i have to add it to group by expression.

I am stuck there.

+3


source to share


2 answers


I think the request 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 other way to do this, but when it comes to ranking, the first / last function is very useful

+2


source


try it



 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
+1


source






More articles:


All Articles