with
wt as -- смены
  (
  select
    resource_id,
    resource_type,
    trunc(time_start) as shift_date,
    round((cast(min(time_end) as date) - cast(max(time_start) as date)) * 24 * 60) as minutes
  from
    resource_schedule
  where
    resource_type='T'
    and event_type='A'
    and trunc(time_start) between cast(trunc(sysdate) as date)-3 and cast(trunc(sysdate) as date)+4
  group by
    resource_id,
    resource_type,
    trunc(time_start)
  having
    cast(min(time_end) as date) - cast(max(time_start) as date) > 0
  ),
bk as  -- букинги
  (
  select
    br.resource_type,
    br.resource_id,
    trunc(b.time_start) as shift_date,
    round(sum(cast(b.time_end as date) - cast(b.time_start as date)) * 24 * 60) as minutes,
    mi.fgrp,
    count(mi.fgrp) as count
  from
    bookings b,
    booking_resources br,
    menu_items mi
  where
    b.booking_id = br.booking_id
    and trunc(b.time_start) between cast(trunc(sysdate) as date)-3 and cast(trunc(sysdate) as date)+4
    and b.status != 'V'
    and br.resource_type in ('T', 'F')
    and mi.mi_id=b.mi_id
  group by
    br.resource_type,
    br.resource_id,
    trunc(b.time_start),
    mi.fgrp
  having
    sum(cast(b.time_end as date) - cast(b.time_start as date)) > 0
  ),
chk as -- чеки
  (
    select
      trunc(dd.tm) shift_date,
      dd.comission_empl_id,
      sum(dd.ttl - dd.dsc) ttl,
      count(distinct d.def_card_id) guests
    from
      docs d, docs_dtl dd
    where
      dd.doc_id = d.doc_id and dd.tm between cast(trunc(sysdate) as date)-3 and cast(trunc(sysdate) as date)+4
    group by
      trunc(dd.tm),
      dd.comission_empl_id
  )
select
  nvl(wt.shift_date, bk.shift_date) as shift_date,
  nvl(wt.resource_type, bk.resource_type) as resource_type,
  nvl(wt.resource_id, bk.resource_id) as resource_id,
  case
    when nvl(wt.resource_type, bk.resource_type) = 'T' then
      (select name2 || ' ' || name1  from employees where empl_id = nvl(wt.resource_id, bk.resource_id))
    when nvl(wt.resource_type, bk.resource_type) = 'F' then
      (select name from facilities where facilities_id = nvl(wt.resource_id, bk.resource_id))
  end as resource_name,
  case
    when nvl(wt.resource_type, bk.resource_type) = 'T' then 'Сотрудники'
    when nvl(wt.resource_type, bk.resource_type) = 'F' then 'Помещения'
  end as resource_type_text,
  round(nvl(bk.minutes, 0) / nvl(wt.minutes, case when bk.resource_type = 'T' then bk.minutes else 12 * 60 end) * 1000 ) /10 as utilization,
  case
    when  nvl(wt.resource_type, bk.resource_type) = 'T' then
    nvl(round(chk.ttl/chk.guests * 100)/100, 0)
  else 0 end check_avg,
  case
    when  nvl(wt.resource_type, bk.resource_type) = 'T' then
    nvl(chk.guests, 0)
  else 0 end check_cnt,
  to_char(nvl(wt.shift_date, bk.shift_date), 'DD.MM.YY') as shift_date_text,
  case when nvl(wt.shift_date, bk.shift_date) > cast(trunc(sysdate) as date)
    then nvl(wt.shift_date, bk.shift_date) - cast(trunc(sysdate) as date)
    else 0 end as shift_num
    ,bk.fgrp, bk.count

from
wt full outer join bk
  on wt.resource_id = bk.resource_id and wt.resource_type = bk.resource_type
    and wt.shift_date = bk.shift_date
left join chk
  on (chk.comission_empl_id = nvl(wt.resource_id, bk.resource_id) and chk.shift_date=nvl(wt.shift_date, bk.shift_date))
where bk.resource_type='T'
--and bk.shift_date=trunc(sysdate)
order by 1,2,4