---
title: "SQL-028: Association period date range retrieval"
summary: Retrieve effective and expiration dates for issued periods.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-31
library: SAIF.Business.View.Policy
reference_type: package
---

# SQL-028: Association period date range retrieval

`clsAssociationTypeCode` uses this statement to attach a coverage date range to each associated policy shown in a policy header. Only the most recent issued period counts, where "issued" means a printed `P401` endorsement or a non-WCIS period in status `E`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-028 |
| **Operation type** | Read |
| **Execution method** | Inline SQL with `String.Format` substitution |
| **Database** | Oracle |
| **Schema** | `[unqualified]`** |
| **Command type** | Text |
| **Business object** | `clsAssociationTypeCode` |
| **Criteria property** | N/A (module-level `Const`, not `IBusinessCriteria`) |
| **Lifecycle method** | `GetPeriodDates` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `{0}` (substituted list) | Input (literal substitution) | Decimal list | N/A | Comma-joined list of policy numbers written directly into the statement text by `String.Format`; not a bind variable. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [POLICY_PERIOD_ALL](../database-tables.md#policy-period-all) | SELECT | POLICY_NUM, POLICY_PERIOD_EFF_DATE, POLICY_PERIOD_EXP_DATE, CANCEL_DATE |
| [ENDORSEMENT](../database-tables.md#endorsement) | JOIN | POLICY_NUM, POLICY_PERIOD_EFF_DATE, ENDORSE_CODE, PRINTED_DATE |
| [POLICY_PERIOD_NON_WCIS](../database-tables.md#policy-period-non-wcis) | JOIN | POLICY_NUM, POLICY_PERIOD_EFF_DATE, POLICY_PERIOD_STATUS_CODE |

## Sample SQL

```sql
select pp.policy_num as PolicyNumber, pp.policy_period_eff_date as EffectiveDate,
       nvl(pp.cancel_date, pp.policy_period_exp_date) as ExpirationDate
from policy_period_all pp
join (  -- get the most recent issued period per policy
       select  pp.policy_num, max(pp.policy_period_eff_date) as policy_period_eff_date
       from policy_period_all pp
       join (      -- get all issued periods for all policies
                   select policy_period_eff_date, policy_num
                     from endorsement
                    where endorse_code = 'P401' and
                          printed_date IS NOT NULL
               union
                   select policy_period_eff_date, policy_num
                     from policy_period_non_wcis
                    where policy_period_status_code = 'E'
             ) issued_periods on pp.policy_num = issued_periods.policy_num and
                                 pp.policy_period_eff_date = issued_periods.policy_period_eff_date
       where pp.policy_period_eff_date <> nvl(cancel_date, policy_period_exp_date)
       group by pp.policy_num
   ) max_periods on pp.policy_num = max_periods.policy_num and
                    pp.policy_period_eff_date = max_periods.policy_period_eff_date
where pp.policy_num in ({0})
```

## Operations and processes

- [Resolve policy-header associations](../operations/resolve-policy-header-associations.md)

## Business rules

- [BR-014: Association date defaults differ per association family and detail-load failures are non-fatal](../business-rules.md#br-014-association-date-defaults-differ-per-association-family-and-detail-load-failures-are-non-fatal)

> **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.View.Policy/Associations/clsAssociationTypeCode.vb`