--- a/schema/reports.sql	Wed Jul 07 19:24:03 2021 +0200
+++ b/schema/reports.sql	Thu Jul 08 00:14:58 2021 +0200
@@ -23,11 +23,12 @@
                                          - interval '1 day'),
                                      '1 day'::interval ) d ),
      g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc,
+                   min((location).hectometre) AS hm,
                    objname, array_agg(distinct(location)) AS locations
             FROM waterway.gauges,d
             GROUP BY objname,d.d,(location).country_code,d.ym ),
      measure AS (
-       SELECT g.ym, g.cc, g.objname, g.day,
+       SELECT g.ym, g.cc, g.hm, g.objname, g.day,
               CASE WHEN count(measure_date) = 0
               THEN 1 ELSE 0 END AS missing
          FROM g
@@ -35,21 +36,22 @@
            ON ARRAY[location] <@ g.locations
              AND g.day <= measure_date
              AND measure_date < (g.day + interval '1 day')
-         GROUP BY g.objname,g.day,g.ym,g.cc )
-  SELECT cc, ym AS month, objname, sum(missing) AS daynodata
+         GROUP BY g.objname,g.day,g.ym,g.cc,g.hm )
+  SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata
     FROM measure
-    GROUP BY cc, ym, objname;
+    GROUP BY cc, ym, objname, hm;
 
 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
-               FROM waterway.bottlenecks ),
+     bn AS ( SELECT DISTINCT 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, d.ym AS month, bid.objnam,
+  SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
          COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
          COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
     FROM bn, bid
@@ -66,7 +68,7 @@
          AND efa.measure_date < (d.ym + interval '1 month')
          AND efa.measure_type = 'Measured'
     WHERE bid.objnam = bn.objnam
-    GROUP BY bn.cc, d.ym, bid.objnam;
+    GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;
 
 -- We need a wrapper procedure with owner rights for
 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW