--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1470/01.update_report.sql	Thu Jun 01 12:24:18 2023 +0200
@@ -0,0 +1,30 @@
+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 bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
+                    min ((lower(stretch)).hectometre) AS hm
+               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
+    CROSS JOIN d
+    LEFT OUTER JOIN waterway.sounding_results sr
+      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 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'
+    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;
\ No newline at end of file