Versions Compared

Old Version 23

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 SELECT t1.ValidFrom, t1.FromDate, t1.ToDate, t1.SorId, t1.ParentSorId, t1.HealthInstitutionSorId,
       t1.ProviderId, t1.ShakId, t1.EntityName, t1.InstitutionOwnerSorId,
       t1.InstitutionOwnerCvrNumberId, t1.EntityTypeId, t1.EntityTypeName
FROM SOR2SorEntity t1
JOIN (
        SELECT UniqueCurrentKey, MAX(ValidFrom) as MaxValidFrom
        FROM SOR2SorEntity
        WHERE FromDate > :date_cutoff
        OR (FromDate = 
                (
 	                SELECT MAX(FromDate)
                	FROM SOR2SorEntity t3
                	WHERE t3.SorId = SOR2SorEntity.SorId
                    AND 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 isIS NULL OR t2.ToDate > :date_cutoff)
        )
        GROUP BY t2.UniqueCurrentKey
) t4
 t2 ON t1.UniqueCurrentKey = t2t4.UniqueCurrentKey
 AND t1.ValidFrom = t2t4.MaxValidFrom
;

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

...