Sum booking days for a given month

from the Artful Common Queries page

A table tracks beginning and ending booking dates dfrom and dto. How to sum booking durations in days for a given month, say July? A booking may begin and end in the target month, or begin, or end, or neither: 
select 
  sum(
    case 
       when month(dfrom)=7 and month(dfrom)=month(dto) then datediff(dto,dfrom)
       when month(dfrom)=7                             then datediff(last_day(dfrom),dfrom)
       when month(dto)=7                               then datediff(dto,date_format(dto,'%Y-%m-01'))
       else 0
     end
  ) as DaysInJuly
  from ...

Last updated 10 Nov 2024