Which table has Leave Taken Column in HR Module

Frappe HR
1

Hi,
I want to create a report which will give a total of all type of leaves
like EL+CL=XXX
I am not able to find the “leave availed/taken” column. Can anyone tell me in which table I can find that table so that I can use a join and extract the data.

Regards
Ruchin Sharma

2

i believe that data is not stored in a table but needs to be computed based on the leave applications.

3

Then why it doesn’t show anything
SELECT
l.employee as “Employee:Link/Employee:120”,
l.employee_name as “Employee Name::220”,
l.leave_type as “Leave Type:Link/Leave Type:220”,
k.new_leaves_allocated as “Leaves Allocated::110”,
l.total_leave_days as “Leave Taken::220”
FROM tabLeave Application l,
tabLeave Allocation k
WHERE l.employee=k.employee
AND l.fiscal_year=k.fiscal_year
AND l.leave_type=k.leave_type
AND l.docstatus=k.docstatus
AND k.docstatus=1

I have also tried this:

SELECT
l.employee as “Employee:Link/Employee:120”,
l.employee_name as “Employee Name::220”,
l.leave_type as “Leave Type:Link/Leave Type:220”,
k.new_leaves_allocated as “Leaves Allocated::110”,
l.total_leave_days as “Leave Taken::220”
FROM tabLeave Application l,
tabLeave Allocation k
WHERE IFNULL(l.employee,0)=IFNULL(k.employee,0)
AND IFNULL(l.fiscal_year,0)=IFNULL(k.fiscal_year,0)
AND IFNULL(l.leave_type,0)=IFNULL(k.leave_type,0)
AND IFNULL(l.docstatus,0)=IFNULL(k.docstatus,0)
AND k.docstatus=1

4

@neilLasrado

5

@ruchin78 you will need to use the sum function in query. I am still not able to understand what you are actually trying to achieve. but your query should be like select fields , sum (leave from leave application ) from table1, table2 where table1.emp = table2.emp
Does this help?

6

Dear Sir,
I know the query as well as far as join are concern I had to put more joins because I want to use:

  1. Same Leave Type
  2. Should Fall in Same Financial Year
  3. Should Have Same DocStatus
  4. DocStatus shouldn’t be cancelled.
    Actually I want to know the total of CL+EL
    Also, as of now there is no data in Leave Application Table therefore I have to use IFNULL otherwise the join condition will fail.
7

Here is the solution:

SELECT
Employee as “Employee:Link/Employee:120”,
Employee_Name as “Employee_Name::220”,
SUM(Casual_Leaves) as “Casual Leaves::120”,
SUM(Earned_Leaves) as “Earned Leaves::120”,
SUM(Casual_Leaves)+SUM(Earned_Leaves) as “Balance::120” FROM (
SELECT
null as “Employee”,
null as “Employee_Name”,
null as “Casual_Leaves”,
null as “Earned_Leaves”,
null as “Balance”
FROM DUAL
UNION
SELECT
k.employee as “Employee:Link/Employee:120”,
k.employee_name as “Employee_Name::220”,
IFNULL(k.new_leaves_allocated,0) as “Casual_Leaves::110”,
0 as “Earned_Leaves”,
null as “Balance”
FROM tabLeave Allocation k
WHERE k.docstatus=1
AND k.leave_type=‘Casual Leave’
UNION
SELECT
k.employee as “Employee:Link/Employee:120”,
k.employee_name as “Employee_Name::220”,
0 as “Casual_Leaves::110”,
IFNULL(k.new_leaves_allocated,0) as “Earned_Leaves”,
null as “Balance”
FROM tabLeave Allocation k
WHERE k.docstatus=1
AND k.leave_type=‘Earned Leave’
) x WHERE x.Employee is not null
GROUP BY Employee,
Employee_Name

Pasted image822×376 18.7 KB