Operation Details
Description: Reads evidenceGroupInfo by evidenceGroupNameCode and effective date. The maximum effective date is calculated, this must be before that specified cut-off date. This will result in a number of dates each one for a different group. The group id is match to the evidence group name code provided to determine the correct group info id.
Sterotype: ns
SQL 
SELECT
  egi.evidenceGroupInfoID
INTO
  :evidenceGroupInfoID 
FROM
  EvidenceGroup,
  
  ( 
  SELECT
    MAX(effectiveDate) maxeffectivedate,
    evidenceGroupID 
  FROM
    EvidenceGroupInfo 
  WHERE effectiveDate <= :effectiveDate 
  GROUP
    BY evidenceGroupID 
  ) egimax,
  EvidenceGroupInfo egi 
WHERE EvidenceGroup.evidenceGroupID = egimax.evidenceGroupID 
  AND egimax.maxeffectivedate = egi.effectiveDate 
  AND egimax.evidenceGroupID = egi.evidenceGroupID 
  AND EvidenceGroup.evidenceGroupNameCode = :evidenceGroupNameCode