General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>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
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only