Operation Details
Description: This operation is used to read the nearest active evidence record in the past to the input date, for a given Case, which has an associated Case Evidence Group record of the specified evidence group. 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. The results are restricted to only those that match the evidence group name code from the case evidence group table. Once this is done the resulting date is used to select all data from the corresponding case evidence tree. As many groups may apply to the resultant date, the results must be restricted again to the group name code.
Sterotype: ns
SQL 
SELECT
  allitems.caseEvidenceTreeID,
  allitems.caseID,
  allitems.effectiveFrom,
  allitems.statusCode,
  allitems.previousEvidenceID,
  allitems.versionNo,
  ceg.caseEvidenceGroupID,
  ceg.evidenceGroupInfoID,
  ceg.versionNo
INTO
  :caseEvidenceTreeID,
  :caseID,
  :effectiveFrom,
  :statusCode,
  :previousEvidenceID,
  :versionNo,
  :caseEvidenceGroupID,
  :evidenceGroupInfoID,
  :caseEvidenceGroupVersionNo 
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 
    ) c1,
    CaseEvidenceGroup ceg 
  WHERE ceg.evidenceGroupNameCode = :evidenceGroupNameCode 
    AND ceg.caseEvidenceTreeID = c1.caseEvidenceTreeID 
  ) maxitems,
  CaseEvidenceTree allitems,
  CaseEvidenceGroup ceg 
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 
  AND ceg.caseEvidenceTreeID = allitems.caseEvidenceTreeID 
  AND ceg.evidenceGroupNameCode = :evidenceGroupNameCode