>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?
You can join the table to itself and calculate the MIN date that's greater than the current date (part of the JOIN condition). That'll give you your two dates. You can't calculate the number of days in the same SQL. You can do that in another view or in the form or report or whatever.
select a.customer_id,;
a.status as beginstatus,;
a.denddate as dbegindate,;
b.status as nextstatus,;
min(b.denddate) as denddate;
from customer_Status a JOIN customer_Status b;
ON a.customer_id=b.customer_id and;
b.denddate>a.denddate;
GROUP BY 1