'Want to calculate leave days month wise in mysql

Scenario is if Employee apply for leave: From Date is "2016-03-30" and To Date is "2016-04-02" so Output will be "In 3rd month leave days should be 2" and "In 4th month leave days should be 1". I have Table:

UserID          FromDate      ToDate     LeaveDuration
------------------------------------------------------
0001            20/03/2016    21/03/2016       1
0001            30/03/2016    02/04/2016       2

In 2nd record 2 days should be consider in 3rd month and 1 day should be consider in 4th month. I tried below query:

select sum(datediff(ToDate,FromDate)) as Total
from wp_ag_assign_leave
where UserId=18
  and LeaveType="Carry Forward Leave"
  and (EXTRACT(MONTH FROM CURDATE())=EXTRACT(MONTH FROM FromDate)
  OR EXTRACT(MONTH FROM CURDATE())=EXTRACT(MONTH FROM ToDate))

Please Help me to solve it

mysqlsql


Solution 1:[1]

you may want to precise some dates usind e.g date_add but here's what I've done :

select month,
       sum(duration) as 'LeaveDays'       
from (
  select if(month(FromDate)=month(ToDate),month(FromDate),'other') as 'month',
        if(month(FromDate)=month(ToDate),datediff(Todate, FromDate),'other')  as 'duration'
 from wp_ag_assign_leave
 UNION   
 select  if(month(FromDate)!=month(ToDate),month(FromDate),'other') as 'month',
           if(month(FromDate)!=month(ToDate),datediff(last_day(FromDate),FromDate),'other') as 'duration'
 from wp_ag_assign_leave
 UNION
 select  if(month(FromDate)!=month(ToDate),month(ToDate),'other') as 'month',
           if(month(FromDate)!=month(ToDate),dateDiff(ToDate,last_day(FromDate)),'other') as 'duration'
 from wp_ag_assign_leave
) as parseMonths
where month!='other'
group by month
order by month;

The 3 SELECTin the UNION statement : - 1 the dates are in the same month, simple difference - 2 the dates are NOT in the same month : 1st month's part - 2 the dates are NOT in the same month : 2nd month's part

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions