Re: Find status change of account over a period of time

One option:

Convert each source row to one with an effective period using an OLAP function and apply NORMALIZE; then optionally switch back to two individual DATE columns instead of a PERIOD.

SELECT AccountNumber, Status, BEGIN(Effective_Period) as StartDate, END(Effective_Period) as EndDate
FROM
(
SELECT NORMALIZE AccountNumber, Status,
PERIOD(EffectiveDate,
COALESCE( /* Use ordered analytic function to pick up effective date from next row as period end */
MIN(EffectiveDate) OVER (PARTITION BY AccountNumber ORDER BY EffectiveDate ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
,date'9999-12-31' /* Or if this is last row for the AccountNumber, use high date as period end */
)
) as Effective_Period
FROM SourceTable
) as NormalizedRows