--- a/schema/reports.sql	Thu Jun 01 11:23:19 2023 +0200
+++ b/schema/reports.sql	Thu Jun 01 12:24:18 2023 +0200
@@ -41,34 +41,36 @@
     FROM measure
     GROUP BY cc, ym, objname, hm;
 
+DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats;
+
 CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
 WITH d AS ( SELECT ym::date
               FROM generate_series( '2019-10-01'::date,
                                     now() - interval '1 day',
                                     '1 month'::interval ) ym),
-     bn AS ( SELECT DISTINCT objnam, responsible_country AS cc,
+     bn AS ( SELECT DISTINCT bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
                     min ((lower(stretch)).hectometre) AS hm
-               FROM waterway.bottlenecks GROUP BY objnam,cc),
-     bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids
-               FROM waterway.bottlenecks GROUP BY objnam)
-  SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
+               FROM waterway.bottlenecks
+               WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
+               GROUP BY bottleneck_id,cc
+               )
+  SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
          COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
          COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
-    FROM bn, bid
+    FROM bn
     CROSS JOIN d
     LEFT OUTER JOIN waterway.sounding_results sr
-      ON ARRAY[sr.bottleneck_id] <@ bid.ids
+      ON sr.bottleneck_id = bn.bottleneck_id
          AND d.ym <= sr.date_info
          AND sr.date_info < (d.ym + interval '1 month')
     LEFT OUTER JOIN waterway.fairway_availability fa
-      ON ARRAY[fa.bottleneck_id] <@ bid.ids
+      ON fa.bottleneck_id = bn.bottleneck_id
     LEFT OUTER JOIN waterway.effective_fairway_availability efa
       ON fairway_availability_id = fa.id
          AND d.ym <= efa.measure_date
          AND efa.measure_date < (d.ym + interval '1 month')
          AND efa.measure_type = 'Measured'
-    WHERE bid.objnam = bn.objnam
-    GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
+    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;;
 
 -- We need a wrapper procedure with owner rights for
 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW