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.

This mens that client left. – Rafał Kobiela Oct 05 '17 at 14:13 — Rafał Kobiela, Oct 05 '17 at 14:13

Serkan Arslan · Accepted Answer · 2017-10-05T19:06:17.643

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

edited Oct 05 '17 at 19:06

answered Oct 05 '17 at 15:14

Serkan Arslan

13,158
4
29
44

What is the problem? I tested it with first and second sample data and it returns correct result for both of them. The only problem is it returns additional day row for after max day. But it can be filtered easily. – Serkan Arslan Oct 10 '17 at 10:43
Instead of MONTH(T1.date) - 1 = MONTH(T2.[date]) it should be DATEDIFF(MONTH,T1.date, T2.[date]) = -1 But i should have give another example. With this modification it works perfectly. Thans! – Rafał Kobiela Oct 10 '17 at 11:03

Rotation of clients in SQL Server 2014

1 Answers1