How do I repeat calculations on multiple columns using T-SQL?

Question

I am trying to calculate customer churn from month-to-month. I have a table with customer_key, and 12 monthly flags as columns. I need a result output table to show: Month number (1-12), grouping field (tenure band: <1 yr, 1-3 yrs, 3-5 yrs, 5+ yrs), and churn count. For example:

Month Tenure Churn

Month1 <1yr 1,234;

Month2 <1yr 656;

....

Month12 <1yr 777;

The churn count is calculated by subtracting the number of customers who "exist" in one month, minus the number who "exist" in the next month, indicated by Mon1_Basic_Flag and Mon2_Basic_Flag. Currently, I am using the following code to get this result:

                 SELECT
                    'M01' AS Monthnumber
                    ,case when DATEDIFF(month,inception_dt,@fmonth)<12 then '<1yr'
                      when DATEDIFF(month,inception_dt,@fmonth) between 12 and 36 then '1-3yr'
                      when DATEDIFF(month,inception_dt,@fmonth) between 36 and 60 then '3-5yr'
                      when DATEDIFF(month,inception_dt,@fmonth)>60 then '>5yr' end as tenureband
                  ,SUM(CASE WHEN MON1_BASIC_FLAG >0 THEN 1 ELSE 0 END) -SUM(CASE WHEN MON2_BASIC_FLAG>0 then 1 else 0 end
                    as churn 
                from dbo.customers
                group by inception_dt

                union all

              SELECT
                    'M02' AS Monthnumber
                    ,case when DATEDIFF(month,inception_dt,@fmonth)<12 then '<1yr'
                      when DATEDIFF(month,inception_dt,@fmonth) between 12 and 36 then '1-3yr'
                      when DATEDIFF(month,inception_dt,@fmonth) between 36 and 60 then '3-5yr'
                      when DATEDIFF(month,inception_dt,@fmonth)>60 then '>5yr' end as tenureband
                  ,SUM(CASE WHEN MON2_BASIC_FLAG >0 THEN 1 ELSE 0 END) -SUM(CASE WHEN MON3_BASIC_FLAG>0 then 1 else 0 end
                    as churn 
                from dbo.customers
                group by inception_dt

                union all

                ....

                union all

              SELECT
                    'M11' AS Monthnumber
                    ,case when DATEDIFF(month,inception_dt,@fmonth)<12 then '<1yr'
                      when DATEDIFF(month,inception_dt,@fmonth) between 12 and 36 then '1-3yr'
                      when DATEDIFF(month,inception_dt,@fmonth) between 36 and 60 then '3-5yr'
                      when DATEDIFF(month,inception_dt,@fmonth)>60 then '>5yr' end as tenureband
                  ,SUM(CASE WHEN MON11_BASIC_FLAG >0 THEN 1 ELSE 0 END) -SUM(CASE WHEN MON12_BASIC_FLAG>0 then 1 else 0 end
                    as churn 
                from dbo.customers
                group by inception_dt 

However, repeated 12 times, this code leaves a lot of room for error when making any changes. I want to put this in a loop, so that it repeats the same calculation for each month. I can easily do this in SAS but I have searched everywhere for a SQL translation of the concept. Any suggestions? Thanks!

Answer 1

;WITH cte AS (  /* comment this line out for SQL Server version under 2005 */
  SELECT
    m.MonthInt,
    m.MonthNumber,
    DATEDIFF(month, c.inception_dt, @fmonth) AS MonthDiff,
    COUNT(
      CASE
        WHEN m.MonthInt =  1 AND MON01_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  2 AND MON02_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  3 AND MON03_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  4 AND MON04_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  5 AND MON05_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  6 AND MON06_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  7 AND MON07_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  8 AND MON08_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt =  9 AND MON09_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt = 10 AND MON10_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt = 11 AND MON11_BASIC_FLAG > 0 THEN 1
        WHEN m.MonthInt = 12 AND MON12_BASIC_FLAG > 0 THEN 1
      END
    ) AS MonthFlagCount
  /*INTO #cte*/  /* uncomment this for SQL Server version under 2005 */
  FROM (
    SELECT  1, 'M01' UNION ALL
    SELECT  2, 'M02' UNION ALL
    SELECT  3, 'M03' UNION ALL
    SELECT  4, 'M04' UNION ALL
    SELECT  5, 'M05' UNION ALL
    SELECT  6, 'M06' UNION ALL
    SELECT  7, 'M07' UNION ALL
    SELECT  8, 'M08' UNION ALL
    SELECT  9, 'M09' UNION ALL
    SELECT 10, 'M10' UNION ALL
    SELECT 11, 'M11' UNION ALL
    SELECT 12, 'M12'
  ) AS m (MonthInt, MonthNumber)
    CROSS JOIN dbo.customers c ON
  GROUP BY m.MonthInt, m.MonthNumber, c.inception_dt
)  /* comment this line out for SQL Server version under 2005 */
SELECT
  t1.MonthNumber,
  CASE
    WHEN MonthDiff < 12 THEN '<1yr'
    WHEN MonthDiff <= 36 THEN '1-3yr'
    WHEN MonthDiff <= 60 THEN '3-5yr'
    ELSE '>5yr'
  END AS tenureband,
  t1.MonthGlagCount - t2.MonthFlagCount AS churn
FROM cte t1
  INNER JOIN cte t2 ON t1.MonthInt = t2.MonthInt + 1
/*DROP TABLE #cte*/  /* uncomment this for SQL Server version under 2005 */

---

EDIT: Of course, in case of SQL Server 2000 and earlier cte in the final select should also be replaced with #cte.

Answer 2

You could to create a function do do that.

CREATE TABLE Customers
(
    Inception datetime,
    MON1_BASIC_FLAG int, MON2_BASIC_FLAG int, MON3_BASIC_FLAG int, MON4_BASIC_FLAG int,
    MON5_BASIC_FLAG int, MON6_BASIC_FLAG int, MON7_BASIC_FLAG int, MON8_BASIC_FLAG int,
    MON9_BASIC_FLAG int, MON10_BASIC_FLAG int, MON11_BASIC_FLAG int, MON12_BASIC_FLAG int
)
INSERT INTO Customers VALUES ('2010-01-01', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
GO

CREATE FUNCTION TenureBand(@startDate datetime, @endDate datetime)
RETURNS varchar(5)
BEGIN
    DECLARE @diff int
    SELECT  @diff = DATEDIFF(month, @startDate, @endDate)
    RETURN CASE
        WHEN @diff < 12 then '<1yr'
        WHEN @diff BETWEEN 12 AND 36 THEN '1-3yr'
        WHEN @diff BETWEEN 36 AND 60 THEN '3-5yr'
        ELSE '>5yr'
    END
END
GO

SELECT  Inception,
        [01] - [02] as [M01], [02] - [03] as [M02], [03] - [04] as [M03],
        [04] - [05] as [M04], [05] - [06] as [M05], [06] - [07] as [M06],
        [07] - [08] as [M07], [08] - [09] as [M08], [09] - [10] as [M09],
        [10] - [11] as [M10], [11] - [12] as [M11]
INTO    #TempTable
FROM    (
        SELECT  Inception,
                SUM(MON1_BASIC_FLAG) as [01], SUM(MON2_BASIC_FLAG) as [02],
                SUM(MON3_BASIC_FLAG) as [03], SUM(MON4_BASIC_FLAG) as [04],
                SUM(MON5_BASIC_FLAG) as [05], SUM(MON6_BASIC_FLAG) as [06], 
                SUM(MON7_BASIC_FLAG) as [07], SUM(MON8_BASIC_FLAG) as [08],
                SUM(MON9_BASIC_FLAG) as [09], SUM(MON10_BASIC_FLAG) as [10],
                SUM(MON11_BASIC_FLAG) as [11], SUM(MON12_BASIC_FLAG) as [12]
        FROM Customers
        GROUP BY Inception
) p

SELECT  Inception, 
        [#TempTable] as [MonthNumber], 
        dbo.TenureBand(Inception, getdate()) AS [TenureBand], [Churn]
FROM    (   SELECT  Inception, 
                    [M01], [M02], [M03], [M04], [M05], [M06],
                    [M07], [M08], [M09], [M10], [M11]
            FROM    #TempTable
        ) pvt
UNPIVOT (   [Churn] FOR #TempTable IN
        (   [M01], [M02], [M03], [M04], [M05], [M06], 
            [M07], [M08], [M09], [M10], [M11])
        ) as unpv

Answer 3

Not sure if this is better, but keeping them together may help when you need to edit the query.

SELECT
    M.M AS Monthnumber
    ,case when DATEDIFF(month,inception_dt,@fmonth)<12 then '<1yr'
      when DATEDIFF(month,inception_dt,@fmonth) between 12 and 36 then '1-3yr'
      when DATEDIFF(month,inception_dt,@fmonth) between 36 and 60 then '3-5yr'
      when DATEDIFF(month,inception_dt,@fmonth)>60 then '>5yr' end as tenureband
  ,SUM(CASE WHEN CASE M.M
    WHEN 'M01' THEN MON1_BASIC_FLAG
    WHEN 'M02' THEN MON2_BASIC_FLAG
    --..
    WHEN 'M11' THEN MON11_BASIC_FLAG
    END > 0 THEN 1 ELSE 0 END) -
   SUM(CASE WHEN CASE M.M
    WHEN 'M01' THEN MON2_BASIC_FLAG
    WHEN 'M02' THEN MON3_BASIC_FLAG
    --..
    WHEN 'M11' THEN MON12_BASIC_FLAG
    END > 0 then 1 else 0 end)
    as churn 
from dbo.customers
cross join (
    select 'M01' as M union all select 'M02' union all
    select 'M03' union all select 'M04' union all
    select 'M05' union all select 'M06' union all
    select 'M07' union all select 'M08' union all
    select 'M09' union all select 'M10' union all
    select 'M11') M
group by inception_dt, M.M

If you are using SQL Server 2008, there is an UNPIVOT operator that can help here.

Answer 4

Could you do something like below?

It is incomplete but If you can join between the CTE and your existing query you might be able to accomplish what you are trying...

declare @count int;
set @count = 1;
WITH Months AS (
        SELECT
         [Month] = @count
        UNION ALL 
        SELECT
         [Month] = [Month] + 1
        FROM
         Months
        WHERE
         [Month]< 12)

 SELECT 'M' + CAST([Month] as varchar(2))
                ,case when DATEDIFF(month,inception_dt,@fmonth)<12 then '<1yr'
                  when DATEDIFF(month,inception_dt,@fmonth) between 12 and 36 then '1-3yr'
                  when DATEDIFF(month,inception_dt,@fmonth) between 36 and 60 then '3-5yr'
                  when DATEDIFF(month,inception_dt,@fmonth)>60 then '>5yr' end as tenureband
              ,SUM(CASE WHEN MON1_BASIC_FLAG >0 THEN 1 ELSE 0 END) -SUM(CASE WHEN MON2_BASIC_FLAG>0 then 1 else 0 end
                as churn 
            from dbo.customers
            --JOIN TO Months CTE???
            group by inception_dt
   OPTION (MAXRECURSION 12)

Answer 5

Really, just Cartesian join to a table of 12 rows.

Create a table with 1 column called row_number.

Fill it with 12 rows.

Cartesian that table into your query.

SELECT "M" & Row_number

You'll have to pad the row_number to get the 0 for M02

BTW, There are trickier ways to generate that table of 12 numbers but this is a simple way to learn, you'll always be able to refactor later.

Answer 6

You can do something like this:

declare @m int
set @m = 1

while @m <= 12
begin
    -- Construct and run dynamic SQL query

    set @m = @m + 1
end