---
title: "SQL-008: Retrieve preferred workers by policy window"
summary: Retrieve preferred workers for a policy and date window.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Affiliate
reference_type: package
---

# SQL-008: Retrieve preferred workers by policy window

Retrieve preferred workers for a policy and date window.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-008 |
| **Operation type** | Read |
| **Execution method** | Inline SQL composed from shared constants |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsPolicyPreferredWorkerCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PolicyNumber` | Input | Decimal (`NUMBER`) | N/A | Policy whose preferred workers are listed. |
| `:StartDate` | Input | Date (`DATE`) | N/A | Window start date. |
| `:EndDate` | Input | Date (`DATE`) | N/A | Window end date. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [BUSINESS_AFFILIATE](../database-tables.md#business_affiliate) | SELECT/JOIN | policy number, effective/cancel dates, shared association columns |
| [AFFILIATE](../database-tables.md#affiliate) | JOIN | name columns, birth date, audit columns |

## Sample SQL

```sql
SELECT
  BA.AFFILIATE_SSN as AffiliateID,
  BA.POLICY_NUM as PolicyNumber,
  BA.BUS_SEQ_NUM as BusinessSequenceNumber,
  BA.AFFILIATE_EFF_DATE as EffectiveDate,
  BA.AFFILIATE_CANCEL_DATE as CancelDate,
  BA.AFFILIATE_TYPE_CODE as AffiliateTypeCode,
  BA.CREATE_DATE as CreateDate,
  BA.CREATE_USER as CreateUser,
  BA.UPDATE_DATE as UpdateDate,
  BA.UPDATE_USER as UpdateUser,
  BA.INSURED_CODE as InsuredCode,
  BA.AFFILIATE_DUTIES as Duties,
  BA.REMUNERATION as Remuneration,
  BA.OWNERSHIP_FLAG as OwnershipFlag,
  BA.PERCENT_OWNERSHIP as OwnershipPercent,
  BA.DIRECTOR_FLAG as DirectorFlag,
  BA.AFFILIATE_TITLE_CODE as TitleCode,
  BA.PERSONAL_ELECTION_NUM as PersonalElectionNumber,
  BA.OFFICER_OF_NOTICE_FLAG as OfficerOfNoticeFlag,
  A.AFFILIATE_FIRST_NAME as FirstName,
  A.AFFILIATE_INITIAL as MiddleInitial,
  A.AFFILIATE_LAST_NAME as LastName,
  A.AFFILIATE_SUFFIX as NameSuffix,
  A.AFFILIATE_DOB as BirthDate,
  A.CREATE_DATE as CreateDate,
  A.CREATE_USER as CreateUser,
  A.UPDATE_DATE as UpdateDate,
  A.UPDATE_USER as UpdateUser
FROM
  BUSINESS_AFFILIATE BA,
  AFFILIATE A
WHERE
  BA.AFFILIATE_TYPE_CODE = '2'
  AND BA.AFFILIATE_SSN = A.AFFILIATE_SSN
  AND BA.POLICY_NUM = :PolicyNumber
  AND BA.AFFILIATE_EFF_DATE < :EndDate
  AND nvl(BA.AFFILIATE_CANCEL_DATE, :StartDate + 1) > :StartDate
ORDER BY
  A.AFFILIATE_LAST_NAME,
  A.AFFILIATE_FIRST_NAME,
  A.AFFILIATE_INITIAL,
  A.AFFILIATE_SUFFIX
```

## Operations and processes
- [Retrieve preferred workers for a policy](../operations/retrieve-preferred-workers-for-a-policy.md)
- [Compare and locate affiliate associations](../operations/compare-and-locate-affiliate-associations.md)

## Business rules
- [BR-AFF-004: Affiliate associations join to people by SSN](../business-rules.md#br-aff-004-affiliate-associations-join-to-people-by-ssn)
- [BR-AFF-005: Association windows use overlap semantics](../business-rules.md#br-aff-005-association-windows-use-overlap-semantics)
- [BR-AFF-006: Association lists sort by person name](../business-rules.md#br-aff-006-association-lists-sort-by-person-name)
- [BR-AFF-009: Preferred-worker queries require type code 2](../business-rules.md#br-aff-009-preferred-worker-queries-require-type-code-2)
- [BR-AFF-018: The library exposes retrieve-only data access](../business-rules.md#br-aff-018-the-library-exposes-retrieve-only-data-access)

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Sources
- `Business\SAIF.Business.Policy.Affiliate\clsPolicyPreferredWorkerCollection.vb`