# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1558948932 -7200
# Node ID 096968d5628f81ea7366661a0fe44e6fad7a2b0e
# Parent  850e81f13e969ae225623214c584cf9899a19e52
Added latest sounding result date to bottlenecks_geoserver view.

diff -r 850e81f13e96 -r 096968d5628f schema/geoserver_views.sql
--- a/schema/geoserver_views.sql	Mon May 27 11:21:11 2019 +0200
+++ b/schema/geoserver_views.sql	Mon May 27 11:22:12 2019 +0200
@@ -64,7 +64,12 @@
     fairway_availability_latest AS (
         SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
             FROM waterway.fairway_availability
-            ORDER BY bottleneck_id, date_info DESC)
+            ORDER BY bottleneck_id, date_info DESC),
+    sounding_result_latest AS (
+        SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max
+            FROM waterway.sounding_results
+            GROUP BY bottleneck_id
+            ORDER BY bottleneck_id DESC)
     SELECT
         b.id,
         b.bottleneck_id,
@@ -85,13 +90,16 @@
         fal.date_info AS fa_date_info,
         fal.critical AS fa_critical,
         g.gm_waterlevel,
+        srl.date_max,
         g.forecast_accuracy_3d,
         g.forecast_accuracy_1d
     FROM waterway.bottlenecks b
         LEFT JOIN waterway.gauges_geoserver g
             ON b.gauge_location = g.location AND b.gauge_validity = g.validity
         LEFT JOIN fairway_availability_latest fal
-            ON b.id = fal.bottleneck_id;
+            ON b.id = fal.bottleneck_id
+        LEFT JOIN sounding_result_latest srl
+            ON b.id = srl.bottleneck_id;
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
     SELECT