sql/Pg-upgrade2/fix_datepaid.sql

   1 -- @tag: fix_datepaid
   2 -- @description: Felder datepaid in ar und ap richtig setzen
   3 -- @depends: release_2_6_0
   4
   5 UPDATE ap
   6   SET datepaid = COALESCE((SELECT MAX(at.transdate)
   7                            FROM acc_trans at
   8                            LEFT JOIN chart c ON (at.chart_id = c.id)
   9                            WHERE (at.trans_id = ap.id)
  10                              AND (c.link LIKE '%paid%')),
  11                           COALESCE(ap.mtime::date, ap.itime::date))
  12   WHERE paid <> 0;
  13
  14 UPDATE ar
  15   SET datepaid = COALESCE((SELECT MAX(at.transdate)
  16                            FROM acc_trans at
  17                            LEFT JOIN chart c ON (at.chart_id = c.id)
  18                            WHERE (at.trans_id = ar.id)
  19                              AND (c.link LIKE '%paid%')),
  20                           COALESCE(ar.mtime::date, ar.itime::date))
  21   WHERE paid <> 0;