Fdzfgy

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.

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

You can identify each group of "1" by the number of non-zero statuses before them. If you don't care that the group numbers are sequential:

select t.*,

       (case when status = 1

             then sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date)

        end) as grp

from t;

No subqueries, joins or aggregation.

However, you probably want the numbers sequential (as in your example). For that, a subquery is needed:

select t.*,

       (case when status = 1

             then dense_rank() over (partition by customer_id order by grp1)

        end) as grp

from (select t.*,

             sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date) as grp1

      from t

     ) t

Check this solution please. This adds grouping as per your need

 with cte0 as 

 ( 

   select [customer_id], [mis_date], [status],

     COALESCE(LAG(status) over (order by mis_date), status) oldstatus

   FRom Table1

 ),

 cte1 as ( 

   select cte0.*, 

     case when status = 0 then 

       null 

     else

       COUNT( case when  status != oldStatus and status = 0 then 1  else null end) OVER (ORDER BY mis_date) 

     end + 1 grp

   from cte0

 )

 select * from cte1

 GO

 customer_id | mis_date            | status | oldstatus |  grp

 ----------: | :------------------ | -----: | --------: | ---:

       10003 | 01/01/2014 00:00:00 |      1 |         1 |    1

       10003 | 02/01/2014 00:00:00 |      1 |         1 |    1

       10003 | 03/01/2014 00:00:00 |      0 |         1 | null

       10003 | 04/01/2014 00:00:00 |      0 |         0 | null

       10003 | 05/01/2014 00:00:00 |      0 |         0 | null

       10003 | 06/01/2014 00:00:00 |      1 |         0 |    2

       10003 | 07/01/2014 00:00:00 |      1 |         1 |    2

       10003 | 08/01/2014 00:00:00 |      1 |         1 |    2

       10003 | 09/01/2014 00:00:00 |      1 |         1 |    2

       10003 | 10/01/2014 00:00:00 |      0 |         1 | null

       10003 | 11/01/2014 00:00:00 |      0 |         0 | null

       10003 | 12/01/2014 00:00:00 |      0 |         0 | null

       10003 | 13/01/2014 00:00:00 |      1 |         0 |    3

       10003 | 14/01/2014 00:00:00 |      1 |         1 |    3

       10003 | 15/01/2014 00:00:00 |      1 |         1 |    3

 

Working Fiddle

You can use the ALTER TABLE statement in SQL Server to add a column to a table.
Syntax

The syntax to add a column in a table in SQL Server (Transact-SQL) is:

ALTER TABLE table_name

  ADD column_name column_definition;

Let's look at an example that shows how to add a column in an SQL Server table using the ALTER TABLE statement.

For example:

ALTER TABLE customer

  ADD group VARCHAR(10);

This SQL Server ALTER TABLE example will add a column to the customer table called group.