Operation Details
Description: Search for foreign residencies for the specified person which overlap in time with the specified foreign residency.
Sterotype: nsmulti
SQL 
SELECT
  foreignResidencyID,
  startDate,
  endDate,
  countryCode,
  statusCode
INTO
  :foreignResidencyID,
  :startDate,
  :endDate,
  :countryCode,
  :statusCode 
FROM
  ForeignResidency 
WHERE foreignResidencyID <> :foreignResidencyID 
  AND concernRoleID = :concernRoleID 
  AND countryCode = :countryCode 
  AND statusCode = :activeStatusCode 
  AND 
  (
    
    (
      :endDate >= startDate 
      OR :endDate IS NULL 
    ) 
    AND 
    (
      :startDate <= endDate 
      OR endDate IS NULL 
    ) 
  )