I have a dataset like the below dataset. I want to find the number of nights each id was occupied per month. For some rows, the check-in and checkout dates are in different months. I want to know how to write a query to have the occupancy per month. For example, for id=1, check-in: 2020-01-26 and checkout date: 2020-03-02. How can I have a table that shows January occupancy: 6, Feb occupancy: 29, and March occupancy: 1

CodePudding user response：

first, you need a numbers table or tally table , after you can easily to it using this query :

select c.id,
    case when m.id <> 0 
        then adddate(last_day(adddate(checkin_date, interval m.id -1 month)),interval 1 day)
        else checkin_date 
    end as Checkin_date,
    case when last_day(adddate(checkin_date, interval m.id month)) > checkout_date 
        then checkout_date 
        else last_day(adddate(checkin_date, interval m.id month)) 
    end checout_date,
    datediff(case when last_day(adddate(checkin_date, interval m.id month)) > checkout_date 
                then checkout_date 
                else last_day(adddate(checkin_date, interval m.id month)) end,
             case when m.id <> 0 
                then last_day(adddate(checkin_date, interval m.id -1 month))
                else adddate(checkin_date, interval -1 day) end
            ) daysdiff
from checkins c
join numbers m on m.id <= period_diff(date_format(checkout_date, "%Y%m"),date_format(checkin_date, "%Y%m"))
order by  c.id, checkin_date

this is works for any gap (for more than 1 year)

db<>fiddle here

CodePudding user response：

If I understand correctly, you want month-wise aggregated result of occupied inventory.

You can try below simple aggregate query as based on 'Group by' clause then add more criteria logic based on your need if required

select monthname(check_in) as 'Month', sum(dayofyear(check_out) - dayofyear(check_in)) as 'Occupied_days'
from inventory 
where year(check_in)=year(check_out) 
group by 1;

Note: Above query will work only for dataset where check_in & check_out happened within same year.

Check sample query output here in Fiddle