Group Customers by Status in T-SQL

  • A+
Category:Languages

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!

 


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.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: