-- example query with ~80 rows in the turnus subselect
select * from (select ressourcen.*, gebaeude.bezeichnung as "gebaeude.bezeichnung", gebaeude.gebaeude_id as "gebaeude.gebaeude_id", gebaeude.kurzbezeichnung as "gebaeude.kurzbezeichnung", gebaeude.nummer as "gebaeude.nummer", raeume.bemerkung as "raeume.bemerkung", raeume.flaeche as "raeume.flaeche", raeume.nummer as "raeume.nummer", raeume.raum_id as "raeume.raum_id", raumtypen.bezeichnung as "raumtypen.bezeichnung", raumtypen.raumtyp_id as "raumtypen.raumtyp_id", standorte.kurzbezeichnung as "standorte.kurzbezeichnung", standorte.standort_id as "standorte.standort_id", exists (select * from auftragsressourcen where auftrag_id = '46' and ressource_id = ressourcen.ressource_id) as schon_in_auftrag_verwendet, case when (ressourcen.menge = 1) then (case when exists( select * from ( select bGetNumOfBookedSingleRes(ressourcen.ressource_id, turnus.von, turnus.bis) as da from (select timestamp '2002-10-16 08:00:00'  AS von, timestamp '2002-10-16 08:45:00' AS bis union select timestamp '2002-10-16 08:45:00'  AS von, timestamp '2002-10-16 09:30:00' AS bis union select timestamp '2002-10-21 08:00:00'  AS von, timestamp '2002-10-21 08:45:00' AS bis union select timestamp '2002-10-21 08:45:00'  AS von, timestamp '2002-10-21 09:30:00' AS bis union select timestamp '2002-10-23 08:00:00'  AS von, timestamp '2002-10-23 08:45:00' AS bis union select timestamp '2002-10-23 08:45:00'  AS von, timestamp '2002-10-23 09:30:00' AS bis union select timestamp '2002-10-28 08:00:00'  AS von, timestamp '2002-10-28 08:45:00' AS bis union select timestamp '2002-10-28 08:45:00'  AS von, timestamp '2002-10-28 09:30:00' AS bis union select timestamp '2002-10-30 08:00:00'  AS von, timestamp '2002-10-30 08:45:00' AS bis union select timestamp '2002-10-30 08:45:00'  AS von, timestamp '2002-10-30 09:30:00' AS bis union select timestamp '2002-11-04 08:00:00'  AS von, timestamp '2002-11-04 08:45:00' AS bis union select timestamp '2002-11-04 08:45:00'  AS von, timestamp '2002-11-04 09:30:00' AS bis union select timestamp '2002-11-06 08:00:00'  AS von, timestamp '2002-11-06 08:45:00' AS bis union select timestamp '2002-11-06 08:45:00'  AS von, timestamp '2002-11-06 09:30:00' AS bis union select timestamp '2002-11-11 08:00:00'  AS von, timestamp '2002-11-11 08:45:00' AS bis union select timestamp '2002-11-11 08:45:00'  AS von, timestamp '2002-11-11 09:30:00' AS bis union select timestamp '2002-11-13 08:00:00'  AS von, timestamp '2002-11-13 08:45:00' AS bis union select timestamp '2002-11-13 08:45:00'  AS von, timestamp '2002-11-13 09:30:00' AS bis union select timestamp '2002-11-18 08:00:00'  AS von, timestamp '2002-11-18 08:45:00' AS bis union select timestamp '2002-11-18 08:45:00'  AS von, timestamp '2002-11-18 09:30:00' AS bis union select timestamp '2002-11-20 08:00:00'  AS von, timestamp '2002-11-20 08:45:00' AS bis union select timestamp '2002-11-20 08:45:00'  AS von, timestamp '2002-11-20 09:30:00' AS bis union select timestamp '2002-11-25 08:00:00'  AS von, timestamp '2002-11-25 08:45:00' AS bis union select timestamp '2002-11-25 08:45:00'  AS von, timestamp '2002-11-25 09:30:00' AS bis union select timestamp '2002-11-27 08:00:00'  AS von, timestamp '2002-11-27 08:45:00' AS bis union select timestamp '2002-11-27 08:45:00'  AS von, timestamp '2002-11-27 09:30:00' AS bis union select timestamp '2002-12-02 08:00:00'  AS von, timestamp '2002-12-02 08:45:00' AS bis union select timestamp '2002-12-02 08:45:00'  AS von, timestamp '2002-12-02 09:30:00' AS bis union select timestamp '2002-12-04 08:00:00'  AS von, timestamp '2002-12-04 08:45:00' AS bis union select timestamp '2002-12-04 08:45:00'  AS von, timestamp '2002-12-04 09:30:00' AS bis union select timestamp '2002-12-09 08:00:00'  AS von, timestamp '2002-12-09 08:45:00' AS bis union select timestamp '2002-12-09 08:45:00'  AS von, timestamp '2002-12-09 09:30:00' AS bis union select timestamp '2002-12-11 08:00:00'  AS von, timestamp '2002-12-11 08:45:00' AS bis union select timestamp '2002-12-11 08:45:00'  AS von, timestamp '2002-12-11 09:30:00' AS bis union select timestamp '2002-12-16 08:00:00'  AS von, timestamp '2002-12-16 08:45:00' AS bis union select timestamp '2002-12-16 08:45:00'  AS von, timestamp '2002-12-16 09:30:00' AS bis union select timestamp '2002-12-18 08:00:00'  AS von, timestamp '2002-12-18 08:45:00' AS bis union select timestamp '2002-12-18 08:45:00'  AS von, timestamp '2002-12-18 09:30:00' AS bis union select timestamp '2002-12-23 08:00:00'  AS von, timestamp '2002-12-23 08:45:00' AS bis union select timestamp '2002-12-23 08:45:00'  AS von, timestamp '2002-12-23 09:30:00' AS bis union select timestamp '2002-12-25 08:00:00'  AS von, timestamp '2002-12-25 08:45:00' AS bis union select timestamp '2002-12-25 08:45:00'  AS von, timestamp '2002-12-25 09:30:00' AS bis union select timestamp '2002-12-30 08:00:00'  AS von, timestamp '2002-12-30 08:45:00' AS bis union select timestamp '2002-12-30 08:45:00'  AS von, timestamp '2002-12-30 09:30:00' AS bis union select timestamp '2003-01-01 08:00:00'  AS von, timestamp '2003-01-01 08:45:00' AS bis union select timestamp '2003-01-01 08:45:00'  AS von, timestamp '2003-01-01 09:30:00' AS bis union select timestamp '2003-01-06 08:00:00'  AS von, timestamp '2003-01-06 08:45:00' AS bis union select timestamp '2003-01-06 08:45:00'  AS von, timestamp '2003-01-06 09:30:00' AS bis union select timestamp '2003-01-08 08:00:00'  AS von, timestamp '2003-01-08 08:45:00' AS bis union select timestamp '2003-01-08 08:45:00'  AS von, timestamp '2003-01-08 09:30:00' AS bis union select timestamp '2003-01-13 08:00:00'  AS von, timestamp '2003-01-13 08:45:00' AS bis union select timestamp '2003-01-13 08:45:00'  AS von, timestamp '2003-01-13 09:30:00' AS bis union select timestamp '2003-01-15 08:00:00'  AS von, timestamp '2003-01-15 08:45:00' AS bis union select timestamp '2003-01-15 08:45:00'  AS von, timestamp '2003-01-15 09:30:00' AS bis union select timestamp '2003-01-20 08:00:00'  AS von, timestamp '2003-01-20 08:45:00' AS bis union select timestamp '2003-01-20 08:45:00'  AS von, timestamp '2003-01-20 09:30:00' AS bis union select timestamp '2003-01-22 08:00:00'  AS von, timestamp '2003-01-22 08:45:00' AS bis union select timestamp '2003-01-22 08:45:00'  AS von, timestamp '2003-01-22 09:30:00' AS bis union select timestamp '2003-01-27 08:00:00'  AS von, timestamp '2003-01-27 08:45:00' AS bis union select timestamp '2003-01-27 08:45:00'  AS von, timestamp '2003-01-27 09:30:00' AS bis union select timestamp '2003-01-29 08:00:00'  AS von, timestamp '2003-01-29 08:45:00' AS bis union select timestamp '2003-01-29 08:45:00'  AS von, timestamp '2003-01-29 09:30:00' AS bis union select timestamp '2003-02-03 08:00:00'  AS von, timestamp '2003-02-03 08:45:00' AS bis union select timestamp '2003-02-03 08:45:00'  AS von, timestamp '2003-02-03 09:30:00' AS bis union select timestamp '2003-02-05 08:00:00'  AS von, timestamp '2003-02-05 08:45:00' AS bis union select timestamp '2003-02-05 08:45:00'  AS von, timestamp '2003-02-05 09:30:00' AS bis union select timestamp '2003-02-10 08:00:00'  AS von, timestamp '2003-02-10 08:45:00' AS bis union select timestamp '2003-02-10 08:45:00'  AS von, timestamp '2003-02-10 09:30:00' AS bis union select timestamp '2003-02-12 08:00:00'  AS von, timestamp '2003-02-12 08:45:00' AS bis union select timestamp '2003-02-12 08:45:00'  AS von, timestamp '2003-02-12 09:30:00' AS bis union select timestamp '2003-02-17 08:00:00'  AS von, timestamp '2003-02-17 08:45:00' AS bis union select timestamp '2003-02-17 08:45:00'  AS von, timestamp '2003-02-17 09:30:00' AS bis union select timestamp '2003-02-19 08:00:00'  AS von, timestamp '2003-02-19 08:45:00' AS bis union select timestamp '2003-02-19 08:45:00'  AS von, timestamp '2003-02-19 09:30:00' AS bis union select timestamp '2003-02-24 08:00:00'  AS von, timestamp '2003-02-24 08:45:00' AS bis union select timestamp '2003-02-24 08:45:00'  AS von, timestamp '2003-02-24 09:30:00' AS bis union select timestamp '2003-02-26 08:00:00'  AS von, timestamp '2003-02-26 08:45:00' AS bis union select timestamp '2003-02-26 08:45:00'  AS von, timestamp '2003-02-26 09:30:00' AS bis) as turnus) as belegte where da)then 0 else 1 end) else ressourcen.menge - (select max(anzahl) from (select iGetNumOfBookedRes(ressourcen.ressource_id, turnus.von, turnus.bis) as anzahl from (select timestamp '2002-10-16 08:00:00'  AS von, timestamp '2002-10-16 08:45:00' AS bis union select timestamp '2002-10-16 08:45:00'  AS von, timestamp '2002-10-16 09:30:00' AS bis union select timestamp '2002-10-21 08:00:00'  AS von, timestamp '2002-10-21 08:45:00' AS bis union select timestamp '2002-10-21 08:45:00'  AS von, timestamp '2002-10-21 09:30:00' AS bis union select timestamp '2002-10-23 08:00:00'  AS von, timestamp '2002-10-23 08:45:00' AS bis union select timestamp '2002-10-23 08:45:00'  AS von, timestamp '2002-10-23 09:30:00' AS bis union select timestamp '2002-10-28 08:00:00'  AS von, timestamp '2002-10-28 08:45:00' AS bis union select timestamp '2002-10-28 08:45:00'  AS von, timestamp '2002-10-28 09:30:00' AS bis union select timestamp '2002-10-30 08:00:00'  AS von, timestamp '2002-10-30 08:45:00' AS bis union select timestamp '2002-10-30 08:45:00'  AS von, timestamp '2002-10-30 09:30:00' AS bis union select timestamp '2002-11-04 08:00:00'  AS von, timestamp '2002-11-04 08:45:00' AS bis union select timestamp '2002-11-04 08:45:00'  AS von, timestamp '2002-11-04 09:30:00' AS bis union select timestamp '2002-11-06 08:00:00'  AS von, timestamp '2002-11-06 08:45:00' AS bis union select timestamp '2002-11-06 08:45:00'  AS von, timestamp '2002-11-06 09:30:00' AS bis union select timestamp '2002-11-11 08:00:00'  AS von, timestamp '2002-11-11 08:45:00' AS bis union select timestamp '2002-11-11 08:45:00'  AS von, timestamp '2002-11-11 09:30:00' AS bis union select timestamp '2002-11-13 08:00:00'  AS von, timestamp '2002-11-13 08:45:00' AS bis union select timestamp '2002-11-13 08:45:00'  AS von, timestamp '2002-11-13 09:30:00' AS bis union select timestamp '2002-11-18 08:00:00'  AS von, timestamp '2002-11-18 08:45:00' AS bis union select timestamp '2002-11-18 08:45:00'  AS von, timestamp '2002-11-18 09:30:00' AS bis union select timestamp '2002-11-20 08:00:00'  AS von, timestamp '2002-11-20 08:45:00' AS bis union select timestamp '2002-11-20 08:45:00'  AS von, timestamp '2002-11-20 09:30:00' AS bis union select timestamp '2002-11-25 08:00:00'  AS von, timestamp '2002-11-25 08:45:00' AS bis union select timestamp '2002-11-25 08:45:00'  AS von, timestamp '2002-11-25 09:30:00' AS bis union select timestamp '2002-11-27 08:00:00'  AS von, timestamp '2002-11-27 08:45:00' AS bis union select timestamp '2002-11-27 08:45:00'  AS von, timestamp '2002-11-27 09:30:00' AS bis union select timestamp '2002-12-02 08:00:00'  AS von, timestamp '2002-12-02 08:45:00' AS bis union select timestamp '2002-12-02 08:45:00'  AS von, timestamp '2002-12-02 09:30:00' AS bis union select timestamp '2002-12-04 08:00:00'  AS von, timestamp '2002-12-04 08:45:00' AS bis union select timestamp '2002-12-04 08:45:00'  AS von, timestamp '2002-12-04 09:30:00' AS bis union select timestamp '2002-12-09 08:00:00'  AS von, timestamp '2002-12-09 08:45:00' AS bis union select timestamp '2002-12-09 08:45:00'  AS von, timestamp '2002-12-09 09:30:00' AS bis union select timestamp '2002-12-11 08:00:00'  AS von, timestamp '2002-12-11 08:45:00' AS bis union select timestamp '2002-12-11 08:45:00'  AS von, timestamp '2002-12-11 09:30:00' AS bis union select timestamp '2002-12-16 08:00:00'  AS von, timestamp '2002-12-16 08:45:00' AS bis union select timestamp '2002-12-16 08:45:00'  AS von, timestamp '2002-12-16 09:30:00' AS bis union select timestamp '2002-12-18 08:00:00'  AS von, timestamp '2002-12-18 08:45:00' AS bis union select timestamp '2002-12-18 08:45:00'  AS von, timestamp '2002-12-18 09:30:00' AS bis union select timestamp '2002-12-23 08:00:00'  AS von, timestamp '2002-12-23 08:45:00' AS bis union select timestamp '2002-12-23 08:45:00'  AS von, timestamp '2002-12-23 09:30:00' AS bis union select timestamp '2002-12-25 08:00:00'  AS von, timestamp '2002-12-25 08:45:00' AS bis union select timestamp '2002-12-25 08:45:00'  AS von, timestamp '2002-12-25 09:30:00' AS bis union select timestamp '2002-12-30 08:00:00'  AS von, timestamp '2002-12-30 08:45:00' AS bis union select timestamp '2002-12-30 08:45:00'  AS von, timestamp '2002-12-30 09:30:00' AS bis union select timestamp '2003-01-01 08:00:00'  AS von, timestamp '2003-01-01 08:45:00' AS bis union select timestamp '2003-01-01 08:45:00'  AS von, timestamp '2003-01-01 09:30:00' AS bis union select timestamp '2003-01-06 08:00:00'  AS von, timestamp '2003-01-06 08:45:00' AS bis union select timestamp '2003-01-06 08:45:00'  AS von, timestamp '2003-01-06 09:30:00' AS bis union select timestamp '2003-01-08 08:00:00'  AS von, timestamp '2003-01-08 08:45:00' AS bis union select timestamp '2003-01-08 08:45:00'  AS von, timestamp '2003-01-08 09:30:00' AS bis union select timestamp '2003-01-13 08:00:00'  AS von, timestamp '2003-01-13 08:45:00' AS bis union select timestamp '2003-01-13 08:45:00'  AS von, timestamp '2003-01-13 09:30:00' AS bis union select timestamp '2003-01-15 08:00:00'  AS von, timestamp '2003-01-15 08:45:00' AS bis union select timestamp '2003-01-15 08:45:00'  AS von, timestamp '2003-01-15 09:30:00' AS bis union select timestamp '2003-01-20 08:00:00'  AS von, timestamp '2003-01-20 08:45:00' AS bis union select timestamp '2003-01-20 08:45:00'  AS von, timestamp '2003-01-20 09:30:00' AS bis union select timestamp '2003-01-22 08:00:00'  AS von, timestamp '2003-01-22 08:45:00' AS bis union select timestamp '2003-01-22 08:45:00'  AS von, timestamp '2003-01-22 09:30:00' AS bis union select timestamp '2003-01-27 08:00:00'  AS von, timestamp '2003-01-27 08:45:00' AS bis union select timestamp '2003-01-27 08:45:00'  AS von, timestamp '2003-01-27 09:30:00' AS bis union select timestamp '2003-01-29 08:00:00'  AS von, timestamp '2003-01-29 08:45:00' AS bis union select timestamp '2003-01-29 08:45:00'  AS von, timestamp '2003-01-29 09:30:00' AS bis union select timestamp '2003-02-03 08:00:00'  AS von, timestamp '2003-02-03 08:45:00' AS bis union select timestamp '2003-02-03 08:45:00'  AS von, timestamp '2003-02-03 09:30:00' AS bis union select timestamp '2003-02-05 08:00:00'  AS von, timestamp '2003-02-05 08:45:00' AS bis union select timestamp '2003-02-05 08:45:00'  AS von, timestamp '2003-02-05 09:30:00' AS bis union select timestamp '2003-02-10 08:00:00'  AS von, timestamp '2003-02-10 08:45:00' AS bis union select timestamp '2003-02-10 08:45:00'  AS von, timestamp '2003-02-10 09:30:00' AS bis union select timestamp '2003-02-12 08:00:00'  AS von, timestamp '2003-02-12 08:45:00' AS bis union select timestamp '2003-02-12 08:45:00'  AS von, timestamp '2003-02-12 09:30:00' AS bis union select timestamp '2003-02-17 08:00:00'  AS von, timestamp '2003-02-17 08:45:00' AS bis union select timestamp '2003-02-17 08:45:00'  AS von, timestamp '2003-02-17 09:30:00' AS bis union select timestamp '2003-02-19 08:00:00'  AS von, timestamp '2003-02-19 08:45:00' AS bis union select timestamp '2003-02-19 08:45:00'  AS von, timestamp '2003-02-19 09:30:00' AS bis union select timestamp '2003-02-24 08:00:00'  AS von, timestamp '2003-02-24 08:45:00' AS bis union select timestamp '2003-02-24 08:45:00'  AS von, timestamp '2003-02-24 09:30:00' AS bis union select timestamp '2003-02-26 08:00:00'  AS von, timestamp '2003-02-26 08:45:00' AS bis union select timestamp '2003-02-26 08:45:00'  AS von, timestamp '2003-02-26 09:30:00' AS bis) as turnus) as belegte) end as verfuegbar from ressourcen join raeume using (ressource_id) join gebaeude using (gebaeude_id) join standorte using (standort_id) join raumtypen using (raumtyp_id)) as verfuegbare_ressourcen where verfuegbare_ressourcen.verfuegbar > 0;