---
title: "SQL-007: Retrieve pending premium audits for a processing window"
summary: Reads the premium audit queue for audits due within a period, optionally narrowed to one policy.
status: draft
owner: Premium Audit Engineering
last_reviewed: 2026-08-14
library: SAIF.PremiumAudit.Batch
reference_type: embedded
---

# SQL-007: Retrieve pending premium audits for a processing window

This is the queue read that starts every premium audit batch run. The scheduled
job resolves its processing window from configuration, then calls
`dbo.usp_GetPendingAudits` to obtain the set of audits whose due date falls in
that window and whose status has not yet advanced past `Pending`. The optional
`@PolicyNumber` argument supports the operator-initiated single-policy rerun
described in the operational runbook; when it is omitted the procedure returns
the full window. Rows are returned ordered by due date so that the oldest
backlog is worked first, and the procedure applies its own `NOLOCK`-free read
because the queue table is written concurrently by the policy system.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-007 |
| **Operation type** | Read |
| **Execution method** | Stored procedure |
| **Database** | SQL Server |
| **Schema** | `dbo` |
| **Command type** | StoredProcedure |
| **Business object** | N/A (Application layer) |
| **Criteria property** | N/A |
| **Lifecycle method** | N/A |
| **Application class** | `PendingAuditRepository` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `@AuditPeriodStart` | Input | DateTime | N/A | Inclusive start of the processing window; audits with an earlier due date are excluded. |
| `@AuditPeriodEnd` | Input | DateTime | N/A | Inclusive end of the processing window; supplied as the run date plus the configured look-ahead days. |
| `@PolicyNumber` | Input | VarChar | 12 | Optional single-policy filter for operator-initiated reruns. Pass `NULL` to process the whole window. |
| `@ReturnValue` | ReturnValue | Integer | N/A | Count of queue rows returned; `0` indicates an empty window and causes the batch to exit with return code 3. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| PremiumAuditQueue | SELECT | `AuditQueueId`, `PolicyNumber`, `AuditDueDate`, `AuditStatusCode`, `AttemptCount`, `LockedByProcess` |
| Policy | JOIN | `PolicyNumber`, `PolicyEffectiveDate`, `PolicyExpirationDate`, `ServicingBranchCode` |
| AuditStatus | JOIN | `AuditStatusCode`, `AuditStatusName`, `IsTerminal` |

## Sample SQL

```sql
-- Stored procedure call issued by PendingAuditRepository.GetPendingAudits
EXEC dbo.usp_GetPendingAudits
  @AuditPeriodStart = @AuditPeriodStart,
  @AuditPeriodEnd = @AuditPeriodEnd,
  @PolicyNumber = @PolicyNumber
-- Procedure body resides in the SQL Server database and is version-controlled
-- in db/Procedures/usp_GetPendingAudits.sql
```

```sql
-- Procedure body, from db/Procedures/usp_GetPendingAudits.sql
SELECT
  q.AuditQueueId,
  q.PolicyNumber,
  q.AuditDueDate,
  q.AuditStatusCode,
  q.AttemptCount,
  p.PolicyEffectiveDate,
  p.PolicyExpirationDate,
  p.ServicingBranchCode,
  s.AuditStatusName
FROM dbo.PremiumAuditQueue AS q
  INNER JOIN dbo.Policy AS p
    ON p.PolicyNumber = q.PolicyNumber
  INNER JOIN dbo.AuditStatus AS s
    ON s.AuditStatusCode = q.AuditStatusCode
WHERE q.AuditDueDate >= @AuditPeriodStart
  AND q.AuditDueDate <= @AuditPeriodEnd
  AND s.IsTerminal = 0
  AND q.LockedByProcess IS NULL
  AND (@PolicyNumber IS NULL OR q.PolicyNumber = @PolicyNumber)
ORDER BY
  q.AuditDueDate ASC,
  q.AuditQueueId ASC
```

## Operations and processes
- Load pending audit queue
- Rerun single policy audit

## Business rules
- [BR-AUDIT-004 Audits are processed oldest due date first](../business-rules.md#br-audit-004)
- [BR-AUDIT-011 Terminal audit statuses are never reprocessed](../business-rules.md#br-audit-011)
- [BR-AUDIT-012 Queue rows locked by another process are skipped](../business-rules.md#br-audit-012)

## Sources
- src/SAIF.PremiumAudit.Batch/Data/PendingAuditRepository.vb
- src/SAIF.PremiumAudit.Batch/Data/SqlCommandFactory.vb
- db/Procedures/usp_GetPendingAudits.sql