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 ...




Return to the Artful Common Queries page