I'm trying to figure out how I can iterate through a table to get a count of how many days an id is not in an Active status.

Say I have the following table:

The desired output for this example should be:

I would like to do a DATEDIFF() when the status changes from Active to Inactive.

I can't just do a SELECT for Status <> Active and do a DATEDIFF() on the MAX and MIN dates in the range because it could go ACTIVE in between that range which would make the count different as seen in the example table.

I think I need a LOOP and/or CURSOR with a variable that gets added to as the count grows for each id, I'm just not sure exactly how to execute that.

Any suggestions or ideas are much appreciated!

CodePudding user response：

Try this:

SELECT id,count(*) as days_inactive FROM table
WHERE status like 'inactive' 
GROUP BY id

CodePudding user response：

You can achieve what you want using variables. Just calculate the number of days from the previous date if and only if the id is the same and the current row is an active status, as follows.

select @id :=0, @lastDate := null;
select id, sum(DaysInactive) as DaysInactive
from (
     select id, (case when id = @id and status='Active' then datediff(tranDate, @lastDate) else 0 end) as DaysInactive
     , @id := id, @lastDate := tranDate
     from tablename
     order by id, TranDate
) as calc group by id

You can run the inner select separately to confirm that your calculations are correct. It should also work if your table has consecutive 'Inactive' or 'Active' statuses. Adjust the above for your table and column names.