Versions Compared

Old Version 24

changes.mady.by.user Mads Falch Udengaard

Saved on

compared with

New Version Current

changes.mady.by.user Mads Falch Udengaard

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Cachen indlæses ved kald til "/reload" som beskrevet i driftsvejledningen. Følgende query danner grundlag for at fylde cachen med entiteter fra tabellen SOR2SorEntity. En tilsvarende query er lavet til at indlæse fra SOR2SorShakMap.

SOR2SorEntity:

Code Block
languagesql
linenumberstrue
WITH filtered AS (
  SELECT *
  FROM SOR2SorEntity
  WHERE FromDate > :date_cutoff
  UNION ALL
  SELECT t.*
  FROM SOR2SorEntity t
  JOIN (
    SELECT SorId, MAX(FromDate) AS FromDate
    FROM SOR2SorEntity
    WHERE FromDate <= :date_cutoff
    GROUP BY SorId
  ) m
    ON m.SorId = t.SorId
   AND m.FromDate = t.FromDate
  WHERE t.ToDate IS NULL OR t.ToDate > :date_cutoff
)
SELECT f1.*
FROM filtered f1
JOIN (
  SELECT UniqueCurrentKey, MAX(ValidFrom) AS MaxValidFrom
  FROM filtered
  GROUP BY UniqueCurrentKey
) pick
  ON pick.UniqueCurrentKey = f1.UniqueCurrentKey
 AND pick.MaxValidFrom    = f1.ValidFrom;

SOR2SorShakMap:

Code Block
languagesql
linenumberstrue
SELECT
  t1.ValidFrom,
  t1.FromDate,
  t1.ToDate,
  t1.SorId,
  t1.SorHealthInstitutionSorId,
  t1.ShakId
FROM SOR2SorShakMap t1
JOIN (
  SELECT t2.UniqueCurrentKey, MAX(t2.ValidFrom) AS MaxValidFrom
  FROM SOR2SorShakMap t2
  LEFT JOIN (
    SELECT SorId, MAX(FromDate) AS LatestFromDate
    FROM SOR2SorShakMap
    WHERE FromDate <= :date_cutoff
    GROUP BY SorId
  ) latest ON t2.SorId = latest.SorId
  WHERE t2.FromDate > :date_cutoff
     OR (t2.FromDate = latest.LatestFromDate AND (t2.ToDate IS NULL OR t2.ToDate > :date_cutoff))
  GROUP BY t2.UniqueCurrentKey
) t4
  ON t1.UniqueCurrentKey = t4.UniqueCurrentKey
 AND t1.ValidFrom = t4.MaxValidFrom;

Parameteren ":date_cutoff" udfyldes med property "sores.retention.period" som beskrevet i driftsvejledningen. 

...