BEGIN
declare startdate,enddate date;
declare cnt1,leaveid bigint;
declare cnt int;
declare cur cursor
for
SELECT DISTINCT
  hhlp.leave_period_id
FROM hs_hr_leave_period hhlp WHERE hhlp.fy = FYY;
set cnt1=( SELECT DISTINCT
    COUNT(hhlp.leave_period_id)
  FROM hs_hr_leave_period hhlp WHERE hhlp.fy = FYY);
set cnt=1;
drop temporary table if exists tmp;
create temporary table tmp
(
Leave_Id bigint,
Leave_on date,
Leave_month int,
FY varchar(11)
);
open cur;
fetch next from cur into leaveid;
startloop:loop
if(cnt < cnt1)
then
set startdate=( SELECT
    hhlp.leave_period_start_date
  FROM hs_hr_leave_period hhlp
  WHERE hhlp.leave_period_id = leaveid);
set enddate=( SELECT
    hhlp.leave_period_end_date
  FROM hs_hr_leave_period hhlp
  WHERE hhlp.leave_period_id = leaveid);
while startdate <= enddate
do
if( dayofweek(startdate) <> 1 and dayofweek(startdate) <> 7 )
then
INSERT INTO tmp
  VALUES (leaveid, startdate, MONTH(startdate), FYY);
END IF;
set startdate=date_add(startdate,interval 1 day);
end while;
else 
leave startloop;
end if;
fetch next from cur into leaveid;
set cnt=cnt+1;
end loop;
close cur;
Drop TEMPORARY table if exists tmpresult;
create TEMPORARY table tmpresult
SELECT distinct concat(hhe.EMP_firstname,' ',hhe.EMP_lastname)EMPNAME,
hhe.joined_date,hhjt.jobtit_name,
monthname(STR_TO_DATE(t.Leave_month,'%m'))Lave_MONTH,
COUNT(t.Leave_Id) NO_OF_Day,
hhelq.leave_type_id LEAVETYPE,
t.FY
FROM tmp t
inner join orangehrms.hs_hr_leave hhelq on t.Leave_Id=hhelq.leave_id
inner join orangehrms.hs_hr_employee hhe on hhelq.employee_id=hhe.emp_number
inner join orangehrms.hs_hr_job_title hhjt on hhe.job_title_code=hhjt.jobtit_code
where monthname(STR_TO_DATE(t.Leave_month,'%m'))=leavemonth
group by concat(hhe.EMP_firstname,' ',hhe.EMP_lastname),hhe.joined_date,hhjt.jobtit_name,Leave_month,hhelq.leave_type_id,t.FY; 

drop TEMPORARY table if exists  t1;
create TEMPORARY table t1
SELECT DISTINCT EMPNAME,joined_date,jobtit_name,NO_OF_Day LTY002 from tmpresult tr  where tr.LEAVETYPE='LTY002';

drop TEMPORARY table if exists  t2;
create TEMPORARY table t2
SELECT DISTINCT EMPNAME,joined_date,jobtit_name,NO_OF_Day LTY003 from tmpresult tr  where tr.LEAVETYPE='LTY003';

drop TEMPORARY table if exists  t3;
create TEMPORARY table t3
SELECT DISTINCT EMPNAME,joined_date,jobtit_name,NO_OF_Day LTY006 from tmpresult tr  where tr.LEAVETYPE='LTY006';

select DISTINCT tr.EMPNAME,tr.joined_date,tr.jobtit_name,tr.Lave_MONTH,tr.FY,t1.LTY002,t2.LTY003,t3.LTY006 from tmpresult tr
LEFT  JOIN t1 ON  tr.EMPNAME=t1.EMPNAME and tr.joined_date=t1.joined_date and  tr.jobtit_name=t1.jobtit_name
LEFT  JOIN  t2 ON  tr.EMPNAME=t2.EMPNAME and tr.joined_date=t2.joined_date and  tr.jobtit_name=t2.jobtit_name
LEFT  JOIN t3 ON  tr.EMPNAME=t3.EMPNAME and tr.joined_date=t3.joined_date and  tr.jobtit_name=t3.jobtit_name
order by tr.EMPNAME;

 
 
END