Operation Details
Description: This operation is used to read the nearest evidence record in the past to the input date. The first step is to calculate the maximum effective from date for the required caseID and statusCode before a specified cut-off date - where no date is available null is used. Once this is done the resulting date is used to select all data from the corresponding case evidence tree.
Sterotype: ns
SQL 
SELECT
  allitems.caseEvidenceTreeID,
  allitems.caseID,
  allitems.effectiveFrom,
  allitems.statusCode,
  allitems.previousEvidenceID,
  allitems.versionNo
INTO
  :caseEvidenceTreeID,
  :caseID,
  :effectiveFrom,
  :statusCode,
  :previousEvidenceID,
  :versionNo 
FROM
  
  ( 
  SELECT
    MAX(effectiveFrom) AS maxeffectivefrom 
  FROM
    
    ( 
    SELECT
      * 
    FROM
      CaseEvidenceTree 
    WHERE effectiveFrom IS NOT NULL 
      AND effectiveFrom < :effectiveFrom 
      AND caseID = :caseID 
      AND statusCode = :statusCode 
    
    UNION
      
    SELECT
      * 
    FROM
      CaseEvidenceTree 
    WHERE effectiveFrom IS NULL 
      AND caseID = :caseID 
      AND statusCode = :statusCode 
    ) temp 
  ) maxitems,
  CaseEvidenceTree allitems 
WHERE 
  (
    
    (
      maxeffectivefrom IS NOT NULL 
      AND maxeffectivefrom = allitems.effectiveFrom 
    ) 
    OR 
    (
      maxeffectivefrom IS NULL 
      AND allitems.effectiveFrom IS NULL 
    ) 
  ) 
  AND allitems.caseID = :caseID 
  AND allitems.statusCode = :statusCode