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

Hi, John

Here's what I came up with. Sample data just to make sure I understood your question well:

create cursor costStatus (costumer_id n(4), status n(2), begindate d)

insert into costStatus (costumer_id, status, begindate) values (1,1,{^2001-11-1})
insert into costStatus (costumer_id, status, begindate) values (1,2,{^2001-11-3})
insert into costStatus (costumer_id, status, begindate) values (1,3,{^2001-11-6})
insert into costStatus (costumer_id, status, begindate) values (1,4,{^2001-11-10})
insert into costStatus (costumer_id, status, begindate) values (2,1,{^2001-11-1})
insert into costStatus (costumer_id, status, begindate) values (2,4,{^2001-11-25})

SELECT alfa.costumer_id, alfa.status, MIN(omega.begindate-alfa.begindate) AS days_in_status ;
FROM costStatus omega ;
INNER JOIN costStatus alfa ON omega.costumer_id = alfa.costumer_id ;
WHERE omega.begindate>alfa.begindate ;
GROUP BY 1, 2

bye