mysql - Calculate date difference in sql for specific year -

i have 2 dates, start_date 20141215 , end_date = 20150115. use sql datediff count dates within year 2015 specify in query. here current sql have written:

select count(leave_id),      sum(case when leave_status = 1 1 else 0 end) pending,      sum(case when leave_status = 2 1 else 0 end) declined,      sum(case when leave_status = 3 1 else 0 end) approved,     sum(case when leave_status = 4 1 else 0 end) rostered,     sum(datediff(end_date, start_date)+1) datetotals     employee_leave      ((year(start_date) = :year) or (year(end_date) = :year))      , employee_id = :emp_id 

thanks

you need fix datediff() consider dates during year. think want:

select count(leave_id),         sum(case when leave_status = 1 1 else 0 end) pending,         sum(case when leave_status = 2 1 else 0 end) declined,         sum(case when leave_status = 3 1 else 0 end) approved,        sum(case when leave_status = 4 1 else 0 end) rostered,        sum(datediff(least(end_date, date(concat_ws('-', :year, 12, 31))),                     greatest(start_date, date(concat_ws('-', :year, 1, 1)))                    ) + 1) datetotals employee_leave  ((year(start_date) = :year) or (year(end_date) = :year)) ,       employee_id = :emp_id