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 tmp2;
create temporary table tmp2
select concat(hhe.EMP_firstname,' ',hhe.EMP_lastname)EMPNAME,hhe.joined_date,hhjt.jobtit_name,
monthname(STR_TO_DATE(t.Leave_month,'%m')) month,hhelq.no_of_days_allotted PreviousBalance,count(t.Leave_Id) No_Of_Days, (hhelq.no_of_days_allotted-count(t.Leave_Id)) ActualBalance from tmp t
inner join orangehrms.hs_hr_employee_leave_quota hhelq 
on t.Leave_Id=hhelq.leave_period_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 t.Leave_on not in(SELECT distinct hhh.date  FROM orangehrms.hs_hr_holidays hhh)
and  hhelq.leave_type_id=leavetype 
group by concat(hhe.EMP_firstname,' ',hhe.EMP_lastname),hhe.joined_date,hhjt.jobtit_name,
t.Leave_month order by EMPNAME,hhelq.leave_type_id;
select * from tmp2 t2 where t2.month=monthname;
end