-- Ottieni le date dinamicamente tramite subquery
SELECT 
  t5.denominazione as hotel, 
  t5.descrizione as tipoCamera, 
  DATE_FORMAT(t5.hotelin, '%d') as hotelin, 
  DATE_FORMAT(t5.hotelout, '%d') as hotelout,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL -1 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as LUN,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL 0 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as MAR,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL 1 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as MER,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL 2 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as GIO,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL 3 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as VEN,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL 4 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as SAB,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL 5 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as DOM,
  SUM(IF(DATE_ADD(DATE(dates.data_inizio), INTERVAL 6 DAY) BETWEEN t5.hotelin AND DATE_SUB(t5.hotelout, INTERVAL 1 DAY),1,0)) as LU2
FROM (
  SELECT 
	(SELECT STR_TO_DATE(descrizione, '%d/%m/%Y') FROM tbl_tabelle WHERE tipoTabella = 'defarrivo' LIMIT 1) as data_inizio,
	(SELECT STR_TO_DATE(descrizione, '%d/%m/%Y') FROM tbl_tabelle WHERE tipoTabella = 'defpart' LIMIT 1) as data_fine
) dates
CROSS JOIN (
  select t2.denominazione, t3.descrizione, hotelin, hotelout
  from tbl_ospiti t1
  left join tbl_hotel t2 on t1.hotelid=t2.hotelid
  left join tbl_tabelle t3 on t1.camera1Prenotazione=t3.codice
  where camera1Prenotazione is not null

  union all

  select t2.denominazione, t3.descrizione, hotelin, hotelout
  from tbl_ospiti t1
  left join tbl_hotel t2 on t1.hotelid=t2.hotelid
  left join tbl_tabelle t3 on t1.camera2Prenotazione=t3.codice
  where camera2Prenotazione is not null
) t5
WHERE t5.denominazione is not null
  AND t5.hotelin < DATE_ADD(dates.data_fine, INTERVAL 2 DAY)
  AND t5.hotelout > DATE_ADD(dates.data_inizio, INTERVAL -1 DAY)
GROUP BY t5.denominazione, t5.descrizione, t5.hotelin, t5.hotelout
ORDER BY t5.denominazione, t5.descrizione, t5.hotelin, t5.hotelout
