-- Usa le variabili nella query principale
SELECT hotelId, denominazione, tipo, tipoStanza,
    SUM(IF(DATE(:data_inizio) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G0,
    SUM(IF(DATE_ADD(DATE(:data_inizio), INTERVAL 1 DAY) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G1,
    SUM(IF(DATE_ADD(DATE(:data_inizio), INTERVAL 2 DAY) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G2,
    SUM(IF(DATE_ADD(DATE(:data_inizio), INTERVAL 3 DAY) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G3,
    SUM(IF(DATE_ADD(DATE(:data_inizio), INTERVAL 4 DAY) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G4,
    SUM(IF(DATE_ADD(DATE(:data_inizio), INTERVAL 5 DAY) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G5,
    SUM(IF(DATE_ADD(DATE(:data_inizio), INTERVAL 6 DAY) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G6,
    SUM(IF(DATE_ADD(DATE(:data_inizio), INTERVAL 7 DAY) BETWEEN hotelin AND DATE_SUB(hotelout, INTERVAL 1 DAY),1,0)) as G7

FROM (
    SELECT t1.hotelId, t2.denominazione, t1.camera1Prenotazione as tipo, t3.descrizione as tipoStanza, 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 t1.camera1Prenotazione > 0 AND t1.hotelId > 0
      AND t1.hotelin != '1000-01-01' AND t1.hotelout != '1000-01-01'

    UNION ALL

    SELECT t1.hotelId, t2.denominazione, t1.camera2Prenotazione as tipo, t3.descrizione as tipoStanza, 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 t1.camera2Prenotazione > 0 AND t1.hotelId > 0
      AND t1.hotelin != '1000-01-01' AND t1.hotelout != '1000-01-01'
) t5

WHERE denominazione IS NOT NULL 
    AND hotelin <= DATE_ADD(DATE(:data_fine), INTERVAL 1 DAY)
    AND hotelout > DATE(:data_inizio)

GROUP BY hotelId, tipo, denominazione, tipoStanza
ORDER BY hotelId, tipo
