SQL_QUERY_GET_OPERATING_HOURS

SQL_QUERY

SELECT b.id as build_id, b.name, c.city_name
FROM public."Buildings" b LEFT JOIN
     public."Cities" c
     ON c.id = b.city_id JOIN
     public."OperatingHours" h
     ON h."BuildingId" = b.id JOIN
	 public."Reservations" r
	 on r."BuildingId"=b.id
WHERE c.id = 3
GROUP BY b.id, c.city_name
HAVING COUNT(*) FILTER (WHERE h.day = 4 AND '13:00:00' BETWEEN h.start AND h.end) > 0 AND
       COUNT(*) FILTER (WHERE h.day = 3 AND '09:00:00' BETWEEN h.start AND h.end) > 0 AND
	   COUNT(*) FILTER (WHERE EXTRACT(DAY FROM r."startDate")=7 AND EXTRACT( DAY FROM r."endDate")=7) > 0 AND
	   COUNT(*) FILTER (
		   WHERE NOT ( 
		   	( to_timestamp(r."startTime", 'HH24:MI'), to_timestamp(r."endTime", 'HH24:MI') ) OVERLAPS (to_timestamp('11:00', 'HH24:MI'), to_timestamp('15:00', 'HH24:MI') ) 
	   		) 
	   ) > 0;