>>I have a consumer status table that contains the consumer id, the consumer's status (active, inactive, etc.) and the begin date of the status (the end date is not included - it's assumed that the previous status ended the day the new status began). I would like to determine the date range (or # of days) that a consumer had a certain status. Is there any way to do this with a SQL statement?
>>
>>TIA
>>
>>John
>
>OTTOMH.
>
>You need to join the table to itself and then compare the dates, finding the closest end date to the beginning. A union with the most current status completes the query.
>
Noting what Dan says, I believe, that with the aggregate function in the having, you can calculate the number of days as follows:

>SELECT A.CustID, A.CustStatus, A.BeginDate, B.BeginDate AS EndDate,  ;
>   <b>B.BeginDate - A.BeginDate AS Duration </b>;
>   FROM Status A INNER JOIN Status B ;
>   ON A.CustID = B.CustID ;
>      AND A.BeginDate < B.BeginDate ;
>   GROUP BY A.CustID ;
>   HAVING MIN (B.BeginDate) ;
>UNION ;
>SELECT  Status.CustID, Status.CustStatus, Status.BeginDate, {} AS EndDate, ;
>    <b>DATE - Status.BeginDate AS Duration </b>
>    FROM Status ;
>    GROUP BY Status.CustID ;
>    HAVING MAX (Status.BeginDate)

> Jay