>>>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.BeginDate - A.BeginDate AS Duration;
>>   FROM Status A INNER JOIN Status B ;
>>   ON A.CustID = B.CustID ;
>>      <b>AND A.CustStatus = B.CustStatus ;</b>
>>      AND A.BeginDate < B.BeginDate ;
>>   GROUP BY A.CustID, <b>A.BeginDate, A.CustStatus </b> ;
>>   <b>ORDER BY 1, 3, 2</b>
>>   HAVING MIN (B.BeginDate) ;
>>UNION ;
>>SELECT  Status.CustID, Status.CustStatus, Status.BeginDate, {} AS EndDate, ;
>>    DATE - Status.BeginDate AS Duration ;
>>    FROM Status ;
>>    GROUP BY Status.CustID ;
>>    HAVING MAX (Status.BeginDate)
>

>> Jay

John --

I just wanted to bring the code up to date with the feedback and new info that you provided. In addition to the new group by columns, it's necessary to join the tables also on the status field.

HTH,

Jay