Блог

У меня есть набор данных, подобный приведенному ниже набору данных. Я хочу найти количество ночей, которые каждый идентификатор был занят в месяц. Для некоторых строк даты заезда и выезда указаны в разные месяцы. Я хочу знать, как написать запрос, чтобы получить заполняемость в месяц. Например, для id = 1, регистрация заезда: 2020-01-26 и дата выезда: 2020-03-02. Как я могу получить таблицу, которая показывает заполняемость января: 6, заполняемость февраля: 29 и заполняемость марта: 1

Результат должен быть таким:

во-первых, вам нужен numbers table или tally table , после того, как вы можете легко получить его с помощью этого запроса :

 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
 

это работает для любого промежутка (более 1 года)

вы можете использовать date_format для отображения месяца :

 select
     date_format(case when m.id <> 0 
        then adddate(last_day(adddate(checkin_date, interval m.id -1 month)),interval 1 day)
        else checkin_date 
    end, '%Y %M') as month_year
    ,sum(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
            )) Occupancy
from checkins c
join numbers m on m.id <= period_diff(date_format(checkout_date, "%Y%m"),date_format(checkin_date, "%Y%m"))
group by date_format(case when m.id <> 0 
        then adddate(last_day(adddate(checkin_date, interval m.id -1 month)),interval 1 day)
        else checkin_date 
    end, '%Y %M') 
order by month_year
 

  month_year    | Occupancy
 :------------ | --------:
 2020 April    |        20
 2020 February |        29
 2020 January  |         6
 2020 July     |         3
 2020 June     |         2
 2020 March    |         2
 

db<>скрипка здесь

Если я правильно понимаю, вам нужен агрегированный результат по месяцам занятого инвентаря.

Вы можете попробовать ниже простой агрегированный запрос, основанный на предложении ‘Group by’, а затем добавить дополнительную логику критериев, основанную на ваших потребностях, если требуется

 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;
 

Примечание: Приведенный выше запрос будет работать только для набора данных, где check_in и check_out произошли в течение того же года.

Проверьте пример вывода запроса здесь, в Fiddle