>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.

SELECT A.CustID, A.CustStatus, A.BeginDate, B.BeginDate AS EndDate ;
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 ;
FROM Status ;
GROUP BY Status.CustID ;
HAVING MAX (Status.BeginDate)

Jay