Got a problem with a query I'm trying to write. I have a table that lists people that have been sent an email. There is a bit column named Active which is set to true if they have responded. But I need to count the number of consecutive emails the person has been inactive since either their first email or last active email.

For example, this basic table shows one person has been sent 9 emails. They have been active within two of the emails (3 & 5). So their inactive count would be 4 as we are counting from email number 6 onwards.

PersonID(int)    EmailID(int)    Active(bit)
1                1               0
1                2               0
1                3               1
1                4               0
1                5               1
1                6               0
1                7               0
1                8               0
1                9               0

Any pointers or help would be great.

Regards Greg