>I realize that I'm coming in a little late but it's an interesting problem. What about this:
>
>SELECT
> a.clientid,
> a.status,
> a.effdate AS startdate,
> MIN(b.effdate) AS enddate,
> DATEDIFF(day, a.effdate, ISNULL(MIN(b.effdate), GETDATE())) AS length
>FROM
> status a
> LEFT OUTER JOIN status b ON a.clientid = b.clientid AND b.effdate > a.effdate
>GROUP BY
> a.clientid,
> a.status,
> a.effdate
>ORDER BY 1, 3
>
>I had the Query Analyzer open so the query's written in TSQL. I can't think of any reason that it's wouldn't work in VFP with a little tweaking.
>
>-Mike

Mike --

I like your use of the outer join syntax with the test for null. That would eliminate the need for the UNION.

The question I have to remember to ask particularly in a self-join is: what is the identifier for a row (otherwise mini-Cartesian joins result). I think you've got it.

Jay