I want to get employee leaves whose lvdays sum is greater than 30 with all three table details . Not able to find a way out for this .
employee :
empcode| MNGR_CODE| MNGR_CODE2
-------|----------|-----------
AA | bb | cc
FF | bb | cc
lvtype :
typeid| desc
------|---------
1 | casual
leave :
leaveid| empcode| lvdays| typeid| Flag| date | reason
-------|--------|-------|-------|-----|-----------|---------
1 | AA | 2 | 1 | 1 | 11-02-2020| NULL
2 | AA | 3 | 1 | 1 | 02-11-2019| NULL
3 | FF | 4 | 1 | 1 | 23-12-2019| NULL
Here is the query i am trying to build but not able to achieve the goal .
SELECT MNGR_CODE,
MNGR_CODE2,
lt.desc,
l.reason,
l.leaveID
FROM Leave l
INNER JOIN lvtype lt ON l.typeid = lt.typeid
LEFT OUTER HASH JOIN employee em ON em.Emp_Code = l.empcode
WHERE empcode IN ('AA', 'FF')
AND leaveID IN (SELECT leaveID
FROM Leave l
INNER JOIN lvtype lt ON l.typeid = lt.typeid
WHERE l.empcode IN ('AA', 'FF')
GROUP BY l.empcode
HAVING SUM(lvdays) > 30);
If I followed you correctly, you could use window functions:
select *
from (
select
e.mngr_code,
e.mngr_code2,
lt.desc,
l.reason,
l.leaveid,
sum(l.lvdays) over(partition by e.empcode) sum_lvdays
from leave l
inner join lvtype lt on l.typeid = lt.typeid
inner join employee em on em.emp_code = l.empcode
) t
where sum_lvdays > 30
The subquery is based on your existing query, and adds an additional column that does a window sum of all leaves of each employee. The outer query uses the results of the window sum as a filter.
I am unsure that you do need a left join
in the subquery, so I changed it to an inner join
.