Mike,

That did it! My brain hurts from trying to figure this one out. Good to hear from you again!

Thanks

John

>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