Mercurial > gemma

changeset 5646:88fdf143daf1 DQR adjustments

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
finalize report
author Thomas Junk <thomas.junk@intevation.de>
date Mon, 19 Jun 2023 17:21:45 +0200
parents 6fe970d800ec
children 2a1f36ab6e91
files report-templates/data-quality-report.yaml schema/reports.sql schema/updates/1470/01.update_report.sql
diffstat 3 files changed, 6 insertions(+), 6 deletions(-) [+]
line wrap: on
line diff
--- a/report-templates/data-quality-report.yaml	Mon Jun 19 12:46:57 2023 +0200
+++ b/report-templates/data-quality-report.yaml	Mon Jun 19 17:21:45 2023 +0200
@@ -17,9 +17,9 @@
           # BN names
           - type: select
             statement: >
-              SELECT DISTINCT bottleneck_id, objnam AS bnnam, hm
+              SELECT DISTINCT bottleneck_id, concat(bottleneck_id, ' (', objnam, ')') AS bnnam, hm
                 FROM waterway.dqr_bottleneck_stats
-                WHERE cc = {{ cc }} ORDER BY hm, bottleneck_id, objnam;
+                WHERE cc = {{ cc }} ORDER BY hm, bottleneck_id;
             actions:
               - type: assign
                 name: last_row
@@ -49,7 +49,7 @@
               # BN SR-Count
               - type: select
                 statement: >
-                  SELECT concat(bottleneck_id, ' ', objnam), srcnt, fwacnt
+                  SELECT concat(bottleneck_id, ' (', objnam, ')') AS objnam, srcnt, fwacnt
                     FROM waterway.dqr_bottleneck_stats
                     WHERE cc = {{ cc }} AND month = {{ d }}
                     ORDER BY hm, bottleneck_id;
--- a/schema/reports.sql	Mon Jun 19 12:46:57 2023 +0200
+++ b/schema/reports.sql	Mon Jun 19 17:21:45 2023 +0200
@@ -49,7 +49,7 @@
      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', '[)')
+               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,
@@ -68,7 +68,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, bn.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 12:46:57 2023 +0200
+++ b/schema/updates/1470/01.update_report.sql	Mon Jun 19 17:21:45 2023 +0200
@@ -8,7 +8,7 @@
      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', '[)')
+               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,