Cumulative Count of NULL restarting at NOT NULL

user_6859782 0
Viewed 51

I would like to add a column indicating the number invites a person received before they accepted by incrementally counting the number of null columns before a non-null while partitioning over the PERSON_ID and ordering by the INVITED_DATE.

My table has the following format: 

| UNIQUE_ID | PERSON_ID | INVITED_DATE | ACCEPTED_DATE |
| 12345     | 567       | 12-01-18     | NULL          |
| 12346     | 567       | 12-02-18     | NULL          |
| 12347     | 567       | 12-03-18     | NULL          |
| 12348     | 567       | 12-04-18     | 12-04-18      |
| 12349     | 567       | 12-05-18     | NULL          |
| 12350     | 568       | 12-01-18     | NULL          |
| 12351     | 568       | 12-02-18     | 12-02-18      |

The output should ideally look like the following:

| UNIQUE_ID | PERSON_ID | INVITED_DATE | ACCEPTED_DATE | INVITES_BEFORE_ACCEPT |
| 12345     | 567       | 12-01-18     | NULL          |  1                    |
| 12346     | 567       | 12-02-18     | NULL          |  2                    |
| 12347     | 567       | 12-03-18     | NULL          |  3                    |
| 12348     | 567       | 12-04-18     | 12-04-18      |  0                    |
| 12349     | 567       | 12-05-18     | NULL          |  1                    |
| 12350     | 568       | 12-01-18     | NULL          |  1                    |
| 12351     | 568       | 12-02-18     | 12-02-18      |  0                    |

So far I've tried a number iterations of ROW NUMBER with OVER and PARTITION but I've found it will need to be an OUTER APPLY. The following OUTER APPLY counts over the data but doesn't restart the count with a successful accept.

SELECT t.* , invites.INVITES_BEFORE_ACCEPT
FROM table t
OUTER APPLY (
    SELECT COUNT(*) INVITES_BEFORE_ACCEPT
    FROM table t2
    WHERE t.PERSON_ID = t2.PERSON_ID and t.INVITED_DATE < t2.ACCEPTED_DATE
            ) invites
tsql sql-server
1 Answers

One way would be

WITH t
     AS (SELECT *,
                COUNT(ACCEPTED_DATE)
                  OVER (
                    PARTITION BY PERSON_ID
                    ORDER BY INVITED_DATE) AS Grp
         FROM   [table])
SELECT *,
       SUM(CASE
             WHEN ACCEPTED_DATE IS NULL
               THEN 1
             ELSE 0
           END)
         OVER (
           PARTITION BY PERSON_ID, Grp
           ORDER BY INVITED_DATE) AS INVITES_BEFORE_ACCEPT
FROM   t

Demo

user_732260
Related