Mercurial > gemma

changeset 5647:2a1f36ab6e91 DQR adjustments

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
updated report
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 20 Jun 2023 12:04:52 +0200
parents 88fdf143daf1
children e8aa69040bec
files schema/reports.sql schema/updates/1470/01.update_report.sql
diffstat 2 files changed, 18 insertions(+), 8 deletions(-) [+]
line wrap: on
line diff
--- a/schema/reports.sql	Mon Jun 19 17:21:45 2023 +0200
+++ b/schema/reports.sql	Tue Jun 20 12:04:52 2023 +0200
@@ -49,14 +49,19 @@
      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(), '[)')
                GROUP BY bottleneck_id,cc
-               )
-  SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
+               ),
+      bnn AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, objnam, validity
+               FROM waterway.bottlenecks b
+               ORDER BY bottleneck_id,
+               validity DESC )
+  SELECT bn.cc, bn.hm, d.ym AS month, bnn.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
+    JOIN bnn
+      ON bnn.bottleneck_id = bn.bottleneck_id
     LEFT OUTER JOIN waterway.sounding_results sr
       ON sr.bottleneck_id = bn.bottleneck_id
          AND d.ym <= sr.date_info
@@ -68,7 +73,7 @@
          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;
+    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bnn.objnam;
 
 -- We need a wrapper procedure with owner rights for
 -- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
--- a/schema/updates/1470/01.update_report.sql	Mon Jun 19 17:21:45 2023 +0200
+++ b/schema/updates/1470/01.update_report.sql	Tue Jun 20 12:04:52 2023 +0200
@@ -8,14 +8,19 @@
      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(), '[)')
                GROUP BY bottleneck_id,cc
-               )
-  SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
+               ),
+      bnn AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, objnam, validity
+               FROM waterway.bottlenecks b
+               ORDER BY bottleneck_id,
+               validity DESC )
+  SELECT bn.cc, bn.hm, d.ym AS month, bnn.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
+    JOIN bnn
+      ON bnn.bottleneck_id = bn.bottleneck_id
     LEFT OUTER JOIN waterway.sounding_results sr
       ON sr.bottleneck_id = bn.bottleneck_id
          AND d.ym <= sr.date_info
@@ -27,7 +32,7 @@
          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;
+    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bnn.objnam;
 
 -- Refresh access rights!
 GRANT SELECT on ALL tables in schema waterway TO waterway_user;
\ No newline at end of file