Group Customers by Status in T-SQL

Question

I have a table like this:

customer_id mis_date  status
----------------------------
 10003       2014-01-01  1   
 10003       2014-01-02  1    
 10003       2014-01-03  0   
 10003       2014-01-04  0   
 10003       2014-01-05  0   
 10003       2014-01-06  1   
 10003       2014-01-07  1    
 10003       2014-01-08  1    
 10003       2014-01-09  1    
 10003       2014-01-10  0   
 10003       2014-01-11  0   
 10003       2014-01-12  0   
 10003       2014-01-13  1     
 10003       2014-01-14  1     
 10003       2014-01-15  1     

I'm trying to build the "group" column:

customer_id mis_date status group
----------------------------------
 10003       2014-01-01  1    1
 10003       2014-01-02  1    1
 10003       2014-01-03  0   NULL
 10003       2014-01-04  0   NULL
 10003       2014-01-05  0   NULL
 10003       2014-01-06  1    2
 10003       2014-01-07  1    2
 10003       2014-01-08  1    2
 10003       2014-01-09  1    2
 10003       2014-01-10  0   NULL
 10003       2014-01-11  0   NULL
 10003       2014-01-12  0   NULL
 10003       2014-01-13  1     3
 10003       2014-01-14  1     3
 10003       2014-01-15  1     3

Does anyone know how I can build this group column?

The logic: each day I'm tracking after the customer status and I want to know in each day what is the number of times that that status happened in the customer history but only when he is in the status.

For example: first_time - 1, second_time - 2 etc.

I am kicking my head off and can't find a solution. I guess it's not so complex.

Thanks!

Answer 1

Something like this should work:

;WITH CTE AS (
   SELECT customer_id, mis_date, status,
          ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY mis_date) - 
          ROW_NUMBER() OVER (PARTITION BY customer_id, status ORDER BY mis_date) AS grp
   FROM mytable
), CTE2 AS (
   SELECT customer_id, status, grp, 
          ROW_NUMBER() OVER (ORDER BY MIN(mis_date)) AS rn
   FROM CTE
   WHERE status = 1
   GROUP BY customer_id, status, grp 
)
SELECT c.customer_id, c.mis_date, c.status, rn       
FROM CTE c
LEFT JOIN CTE2 c2 
   ON c.customer_id = c2.customer_id AND c.status = c2.status AND c.grp = c2.grp
ORDER BY mis_date

CTE identifies islands of consecutive records having the same status value. CTE2 enumerates status = 1 subgroups.

Answer 2

Another approach of doing it without CTE is like following query.

SELECT customer_id, mis_date, status, 
       CASE WHEN status = 0 THEN NULL ELSE Dense_rank() OVER (ORDER BY rc) END grp 
FROM   (SELECT *, 
               (SELECT CASE WHEN status = 0 THEN 0 
                         ELSE (SELECT Count(status)  FROM   table1 t2 
                               WHERE  t2.mis_date <= t1.mis_date AND status = 0) END grp)rc 
        FROM   table1 t1) t2 
ORDER  BY mis_date 

Output:

+-------------+-------------------------+--------+------+
| customer_id | mis_date                | status | grp  |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-01 00:00:00.000 | 1      | 1    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-02 00:00:00.000 | 1      | 1    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-03 00:00:00.000 | 0      | NULL |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-04 00:00:00.000 | 0      | NULL |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-05 00:00:00.000 | 0      | NULL |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-06 00:00:00.000 | 1      | 2    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-07 00:00:00.000 | 1      | 2    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-08 00:00:00.000 | 1      | 2    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-09 00:00:00.000 | 1      | 2    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-10 00:00:00.000 | 0      | NULL |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-11 00:00:00.000 | 0      | NULL |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-12 00:00:00.000 | 0      | NULL |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-13 00:00:00.000 | 1      | 3    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-14 00:00:00.000 | 1      | 3    |
+-------------+-------------------------+--------+------+
| 10003       | 2014-01-15 00:00:00.000 | 1      | 3    |
+-------------+-------------------------+--------+------+

Online Demo