Rotation of clients in SQL Server 2014

Question

I have a problem with SQL. I have the following table:

declare @t table (date datetime,
          shop VARCHAR(50), 
          client VARCHAR(50)
         );
insert into @t 
VALUES  ('2016-01-15', 'abc','a1'),
        ('2016-01-15', 'abc','b1'),
        ('2016-01-15', 'def','c1'),
        ('2016-01-15', 'def','a1'),
        ('2016-01-15', 'ghi','b1'),
        ('2016-01-15', 'ghi','a1'),
        ('2016-02-15', 'abc','a1'),
        ('2016-02-15', 'abc','b1'),
        ('2016-02-15', 'abc','c1'),
        ('2016-02-15', 'def','a1'),
        ('2016-02-15', 'ghi','b1'),
        ('2016-02-15', 'ghi','a1'),
        ('2016-03-15', 'abc','a1'),
        ('2016-03-15', 'abc','c1'),
        ('2016-03-15', 'def','a1'),
        ('2016-03-15', 'ghi','b1'),
        ('2016-03-15', 'ghi','e1')

I wolud like to calculate client rotation. So for every month, for every shop I have to count how many clients churned, came and how many clients remained as month before. I can't just look at the numbers of clients, but i have to check if the specific name of client appeared a month earlier. All dates look like this: "year-month-15".

So I would like to get table as follows:

 declare @t2 table (date date,
               shop VARCHAR(50), 
               churned INTEGER,
               stayed INTEGER,
               came INTEGER
              );

 INSERT INTO @t2
 VALUES  ('2016-02-15', 'abc', 0, 2, 1),
    ('2016-02-15', 'def', 1, 1, 0),
    ('2016-02-15', 'ghi', 0, 2, 0),
    ('2016-03-15', 'abc', 1, 2, 0),
    ('2016-03-15', 'def', 0, 1, 0),
    ('2016-03-15', 'ghi', 1, 1, 1)

So for example for abc shop after first month 0 clients churned, 2 clients stayed as month before and 1 new client came. Churned means that client left.

Thanks for help. I am using SQL Server 2014

EDIT: One more example: For this data:

 declare @t table (date datetime,
 shop VARCHAR(50), 
 client VARCHAR(50)
     );
     insert into @t 
     VALUES  ('2016-01-15', 'abc','a1'),
    ('2016-01-15', 'abc','b1'),

    ('2016-02-15', 'abc','b1'),
    ('2016-02-15', 'abc','c1'),

    ('2016-03-15', 'abc','z1'),
    ('2016-03-15', 'abc','y1'),
    ('2016-03-15', 'abc','a1')

I should get the following table:

  declare @t2 table (date date,
           shop VARCHAR(50), 
           churned INTEGER,
           stayed INTEGER,
           came INTEGER
          );

  INSERT INTO @t2
  VALUES  
    ('2016-01-15', 'abc', 0, 0, 2),
    ('2016-02-15', 'abc', 1, 1, 1),
    ('2016-03-15', 'abc', 2, 0, 3)

This is simple example with only one shop for clarification.

Answer 1

you can try this

SELECT  
    ISNULL(T1.date, DATEADD(MONTH,1,T2.date)) date, 
    ISNULL(T2.shop,T1.shop) shop, 
    COUNT(CASE WHEN T1.client IS NULL THEN 1 END) churned,
    COUNT(case when (T1.client = T2.client) then 1 end ) stayed,
    COUNT(CASE WHEN T2.client IS NULL THEN 1 END)  came
FROM 
    @t T1
    FULL JOIN @t T2 ON MONTH(T1.date) - 1 = MONTH(T2.[date])
            AND T1.client = T2.client AND T1.shop = T2.shop
GROUP BY 
    ISNULL(T1.date, DATEADD(MONTH,1,T2.date)) ,ISNULL(T2.shop,T1.shop)
ORDER BY [date], shop