Counting consecutive items within SQL Server

Question

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)    EmailDate(datetime)    Active(bit)
1                1               2009-07-18 19:56:20    0
1                2               2009-08-18 19:56:20    0
1                3               2009-09-18 19:56:20    1
1                4               2009-10-18 19:56:20    0
1                5               2009-11-18 19:56:20    1
1                6               2009-12-18 19:56:20    0
1                7               2010-01-18 19:56:20    0
1                8               2010-02-18 19:56:20    0
1                9               2010-03-18 19:56:20    0

Any pointers or help would be great.

Regards Greg

Answer 1

give this a try:

SET NOCOUNT ON
DECLARE @Emails table (PersonID int, EmailID int,  Active bit)
INSERT @Emails VALUES ( 1,1,0)
INSERT @Emails VALUES ( 1,2,0)
INSERT @Emails VALUES ( 1,3,1)
INSERT @Emails VALUES ( 1,4,0)
INSERT @Emails VALUES ( 1,5,1)
INSERT @Emails VALUES ( 1,6,0)
INSERT @Emails VALUES ( 1,7,0)
INSERT @Emails VALUES ( 1,8,0)
INSERT @Emails VALUES ( 1,9,0)
INSERT @Emails VALUES ( 2,1,0)
INSERT @Emails VALUES ( 2,2,0)
INSERT @Emails VALUES ( 2,3,0)
INSERT @Emails VALUES ( 2,4,0)
INSERT @Emails VALUES ( 2,5,0)
INSERT @Emails VALUES ( 2,6,0)
INSERT @Emails VALUES ( 3,1,1)
INSERT @Emails VALUES ( 3,2,1)
INSERT @Emails VALUES ( 3,3,1)
INSERT @Emails VALUES ( 3,4,1)
SET NOCOUNT OFF


SELECT
    e.PersonID,COUNT(e.EmailID) AS CountInactive
    FROM @Emails e
        LEFT OUTER JOIN (SELECT
                             PersonID,MAX(EmailID) AS LastActive
                             FROM @Emails
                             WHERE Active=1
                             GROUP BY PersonID
                        ) dt ON e.PersonID=dt.PersonID
    WHERE e.EmailID>ISNULL(dt.LastActive,0)
    GROUP BY e.PersonID

OUTPUT:

PersonID    CountInactive
----------- -------------
1           4
2           6

(2 row(s) affected)

EDIT after OP's edit, same output as above:

SELECT
    e.PersonID,COUNT(e.EmailID) AS CountInactive
    FROM @Emails e
        LEFT OUTER JOIN (SELECT
                             PersonID,MAX(EmailDate) AS LastActive
                             FROM @Emails
                             WHERE Active=1
                             GROUP BY PersonID
                        ) dt ON e.PersonID=dt.PersonID
    WHERE (e.EmailDate>ISNULL(dt.LastActive,0)) OR dt.PersonID IS NULL
    GROUP BY e.PersonID

Answer 2

My first cut:

SELECT PersonID, COUNT(*) FROM Table T1
WHERE Active = 0 AND EmailDate > 
   (SELECT MAX(EMailDate) FROM Table T2 
       WHERE T2.PersonID = T1.PersonID AND Active = 1)
GROUP BY PersonID

Note that this solution requires that each person answers at least one email. If you want to include people who were inactive from the very first email that was sent to them you need to wrap that (MAX(EmailDate)) term inside some kind of IFNULL() returning a date before the start date of the system for NULLs.

Also, as KM points out below, if someone is not currently inactive (they answered the most recent email) they will not be in the result set. I think that probably meets your needs but, if not, let me know.