Split data into X days' Cohort (SQL Server) Hi I want to split data by x days' cohort.

the table


SELECT 
 --earliest_time, 
 sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day &gt;  0 and by_day &lt;= 10 then 1 else 0 end) day_10,
    sum(case when by_day &gt; 10 and by_day &lt;= 20 then 1 else 0 end) day_20,
    sum(case when by_day &gt; 20 and by_day &lt;= 30 then 1 else 0 end) day_30,
    sum(case when by_day &gt; 30 and by_day &lt;= 40 then 1 else 0 end) day_40,
    sum(case when by_day &gt; 40 and by_day &lt;= 50 then 1 else 0 end) day_50,
    sum(case when by_day &gt; 50 and by_day &lt;=60 then 1 else 0 end) day_60,
    sum(case when by_day &gt; 60 then 1 else 0 end) day_60plus
from cohort_days co


This splits the data into cohorts of 10 days. If I want to change it to 20 days' cohort, then I need to edit them 1 by 1. I want to just define x days' cohort once, then the data can generate the cohort groups itself automatically.

I want to know is there easier way to do this than the above code? For example, I just need to define x days' cohort once at the beginning then I can use loops to iterate over it to generate the rest of the cohort groups.  

A bit like this:

declare @d int
select @d = 20
select * from cohort_days
SELECT 
 sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day &gt; 0 and by_day &lt;= @d then 1 else 0 end) CONCAT('ay_: ', @d),
    select @2d = @d*2
    sum(case when by_day &gt; @d and by_day &lt;= @2d then 1 else 0 end) CONCAT('ay_: ', @2d)
    select @3d = @d*3
    sum(case when by_day &gt; @2d and by_day &lt;= @3d then 1 else 0 end) CONCAT('ay_: ', @3d)
from cohort_days co


If I want to change cohort days to 50 days, I just need to change select @d = 20 to select @d = 50
Your desired outcome is that you can change one value (the cohort width) to have all rows re-allocated to the new groupings.

I'd use a separate table to hold the upper and lower bounds for each cohort. These values can be generated a run-time according to the width parameter. There are two special cases - the day-zero cohort which only ever contains a single value and the last cohort which has an unlimited upper bound. There will have to be a second parameter to say how many "normal" cohorts there must be. To generate this I will use a "numbers" table.

create table #Cohorts
(
    Label       varchar(20) not NULL,
    LowerBound  int         not NULL,
    UpperBound  int         not NULL
);


declare @Width int = 10;
declare @Depth int = 5;

insert #Cohorts
(
    Label,
    LowerBound,
    UpperBound
)
select
    'day_' + CONVERT(varchar(20), ((n + 1) * @Width)),
    1 + (n * @Width),
    ((n + 1) * @Width)
from Numbers
where n &lt;= @Depth

union all

select 'day_0', 0, 0        -- the special case for day_0

union all

select
    'day_' + CONVERT(varchar(20), ((@Depth + 1) * @Width)) + 'plus',  -- a special label for the last cohort
    1 + ((@Depth + 1) * @Width),    -- the upper limit of the dynamic cohorts
    2147483647                      -- the maximum value of an int; pseudo-infinity


For the values given in the question this returns

Label       LowerBound  UpperBound
----------  ----------  ----------
day_0       0           0
day_10      1           10
day_20      11          20
day_30      21          30
day_40      31          40
day_50      41          50
day_60      51          60
day_60plus  61          2147483647


In your original question you have used (open, close] bounds. For simplicity I have used [closed, closed]. It doesn't matter since the bounds are integers and the cohorts are contiguous. It just means your &gt;  and &lt;= becomes my &gt;= and &lt;=. With tinkering your convention could be used equally well.

We assign each data row to a cohort by joining to this temporary table. The counting is done using GROUP BY.

select
    c.Label, COUNT(*)
from your_data_table as d
inner join #Cohorts as c
     on c.LowerBound &lt;= d.by_day
     and d.by_day &lt;= c.UpperBound
group by c.Label;


This will give one row per cohort. To get a single row with a column per cohort requires a PIVOT. There is a T-SQL statement to do this. There are other methods, too, of which your sum(case..) is one.