From 68fd2cb7fc111aa540511fef955b312ebe517cf5 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Tue, 5 May 2026 16:41:30 +0000
Subject: [PATCH] Resolve problem #25

---
 conversion/clean.sql         | 33 +++++++++++++++++++++++++++++++++
 conversion/compare_state.sql | 10 ++++++++++
 conversion/copy_state.sql    | 11 +++++++++++
 3 files changed, 54 insertions(+)

diff --git a/conversion/clean.sql b/conversion/clean.sql
index bd6b052..3869f28 100644
--- a/conversion/clean.sql
+++ b/conversion/clean.sql
@@ -323,6 +323,39 @@ UPDATE follow
         AND follow.fol_time_begin = problem_24.fol_time_begin;
 
 
+-- Problem #25 Follow ends are not last arrivals
+-- There are cases where the old FOLLOW table's columns, "fol_time_end",
+-- is not the last arrival time, fa_time_end, on FOLLOW_ARRIVAL.
+
+-- Save a record of what the discrepencies look like
+WITH spans AS
+  (SELECT fa_fol_date, fa_fol_b_focal_animid
+          , MAX(fa_time_end) AS max_end
+     FROM clean.follow_arrival
+     WHERE fa_b_arr_animid = fa_fol_b_focal_animid
+     GROUP BY fa_fol_date, fa_fol_b_focal_animid)
+SELECT spans.*, follow.fol_time_end
+  INTO problem_25
+  FROM spans
+    JOIN clean.follow
+      ON (follow.fol_date = spans.fa_fol_date
+          AND follow.fol_b_animid = spans.fa_fol_b_focal_animid)
+  WHERE NOT EXISTS
+        (SELECT 1
+           FROM clean.follow
+           WHERE follow.fol_date = spans.fa_fol_date
+                 AND follow.fol_b_animid = spans.fa_fol_b_focal_animid
+                 AND spans.max_end = follow.fol_time_end);
+
+-- "Fix" the follow end time
+UPDATE follow
+  SET fol_time_end = problem_25.max_end
+  FROM problem_25
+  WHERE follow.fol_date = problem_25.fa_fol_date
+        AND follow.fol_b_animid = problem_25.fa_fol_b_focal_animid
+        AND follow.fol_time_end = problem_25.fol_time_end;
+
+
 -- Problems #26 and #27
 -- There can be a mis-match between the first follow_arrival row for the
 -- focal and the follow row, regarding whether the focal started the
diff --git a/conversion/compare_state.sql b/conversion/compare_state.sql
index be87a2d..2cd8404 100644
--- a/conversion/compare_state.sql
+++ b/conversion/compare_state.sql
@@ -28,3 +28,13 @@ SELECT *
       , problem_24.fa_fol_b_focal_animid
       , problem_24.fol_time_begin
       , problem_24.min_start;
+
+
+SELECT 'Problem #25, Follow ends are not last arrivals';
+
+SELECT *
+  FROM problem_25
+  ORDER BY problem_25.fa_fol_date
+      , problem_25.fa_fol_b_focal_animid
+      , problem_25.fol_time_end
+      , problem_25.max_end;
diff --git a/conversion/copy_state.sql b/conversion/copy_state.sql
index 74adbea..21033f1 100644
--- a/conversion/copy_state.sql
+++ b/conversion/copy_state.sql
@@ -31,3 +31,14 @@ CREATE TABLE conv_state.problem_24 AS
   SELECT * FROM problem_24;
 
 GRANT SELECT ON conv_state.problem_24 TO reader, writer;
+
+--
+-- Problem #25
+--
+
+DROP TABLE IF EXISTS conv_state.problem_25;
+
+CREATE TABLE conv_state.problem_25 AS
+  SELECT * FROM problem_25;
+
+GRANT SELECT ON conv_state.problem_25 TO reader, writer;
-- 
2.34.1