question

Are we allowed to adjust the existing index? Add more indexes? I see several "DateLeft" in the future. Shall we expect "now" is September 2010?

Or "viewpoint" is still October 2009, and "DateLeft" column is misspelled and should read "DateLeave"?

Here is my attempt... Bearing in mind that I have only had 1 coffee so far today, this may yet get revised:

-- Matt 1
DECLARE @TempData TABLE (Joined [smallint], Unsubscribed [smallint], Num int)
INSERT INTO @TempData (Joined, Unsubscribed, Num)
SELECT Joined, Unsubscribed, COUNT(*) FROM
 (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined,
         DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed
    FROM registrations) idat
GROUP BY [Joined], [Unsubscribed]

CREATE TABLE #TempMonths (MonthNumber [smallint], Joined [int], Unsubscribed [int], JoinedToDate [int], UnsubscribedToDate [int])
CREATE CLUSTERED INDEX [IX_TempMonths_1] ON #TempMonths (MonthNumber)
INSERT INTO #TempMonths (MonthNumber, Joined)
SELECT Joined, SUM(Num) FROM @TempData
GROUP BY Joined

UPDATE #TempMonths SET Unsubscribed = idat.UnsubscribedCount
FROM #TempMonths tm INNER JOIN
     (SELECT Unsubscribed AS UnsubscribedMonthNumber, SUM(Num) AS UnsubscribedCount FROM @TempData GROUP BY Unsubscribed) idat
  ON [idat].[UnsubscribedMonthNumber] = tm.MonthNumber

DECLARE @PrevJoined [int], @PrevUnsubscribed [int]
SET @PrevJoined = 0
SET @PrevUnsubscribed = 0

UPDATE #TempMonths
   SET @PrevJoined = JoinedToDate = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
  FROM #TempMonths WITH(INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, 
       Joined AS JoinedThisMonth, 
       ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, 
       JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers 
  FROM #TempMonths 
 ORDER BY MonthDate

Here's version 2 (+2 cups of coffee)

-- Matt 2
CREATE TABLE #TempData (
  Joined       SMALLINT,
  Unsubscribed SMALLINT,
  Num          INT     
);

INSERT INTO #TempData (Joined, Unsubscribed, Num)
SELECT   Joined, Unsubscribed, SUM(Num)
FROM     (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined, DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed, [Num]
          FROM   (SELECT   [DateJoined], [DateLeft], COUNT(*) AS Num
                  FROM     registrations WITH (TABLOCKX)
                  GROUP BY DateJoined, DateLeft) AS innerdat) AS idat
GROUP BY [Joined], [Unsubscribed];

CREATE TABLE #TempMonths (
  MonthNumber        SMALLINT,
  Joined             INT     ,
  Unsubscribed       INT     ,
  JoinedToDate       INT     ,
  UnsubscribedToDate INT     
);

CREATE CLUSTERED INDEX [IX_TempMonths_1]
  ON #TempMonths(MonthNumber);

INSERT INTO #TempMonths (MonthNumber, Joined, Unsubscribed)
SELECT td1.Joined, td1.Num, td2.Num
FROM   (SELECT   Joined, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Joined) AS td1
       LEFT OUTER JOIN
       (SELECT   Unsubscribed, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Unsubscribed) AS td2
       ON td2.Unsubscribed = td1.Joined;

DECLARE @PrevJoined AS INT, @PrevUnsubscribed AS INT;

SET @PrevJoined = 0;

SET @PrevUnsubscribed = 0;

UPDATE #TempMonths
SET    @PrevJoined       = JoinedToDate       = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
FROM   #TempMonths WITH (INDEX (0), TABLOCKX)
OPTION (MAXDOP 1);

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, Joined AS JoinedThisMonth, ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers
FROM   #TempMonths;

Here is version 3 - which is faster still on my machine, although only a slight tweak. Interesting to see the results Phil puts out given that 2 is slower on his rig than 1 is.

-- Matt 3
CREATE TABLE #TempData (
  Joined       SMALLINT,
  Unsubscribed SMALLINT,
  Num          INT     
);
CREATE CLUSTERED INDEX [IX_TempData_1]
  ON #TempData(Joined);
CREATE INDEX [IX_TempData_2]
  ON #TempData(Unsubscribed);

INSERT INTO #TempData (Joined, Unsubscribed, Num)
SELECT   Joined, Unsubscribed, SUM(Num)
FROM     (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined, DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed, [Num]
          FROM   (SELECT   [DateJoined], [DateLeft], COUNT(*) AS Num
                  FROM     registrations WITH (TABLOCKX)
                  GROUP BY DateJoined, DateLeft) AS innerdat) AS idat
GROUP BY [Joined], [Unsubscribed];

CREATE TABLE #TempMonths (
  MonthNumber        SMALLINT,
  Joined             INT     ,
  Unsubscribed       INT     ,
  JoinedToDate       INT     ,
  UnsubscribedToDate INT     
);

CREATE CLUSTERED INDEX [IX_TempMonths_1]
  ON #TempMonths(MonthNumber);

INSERT INTO #TempMonths (MonthNumber, Joined, Unsubscribed)
SELECT td1.Joined, td1.Num, td2.Num
FROM   (SELECT   Joined, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Joined) AS td1
       LEFT OUTER JOIN
       (SELECT   Unsubscribed, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Unsubscribed) AS td2
       ON td2.Unsubscribed = td1.Joined;

DECLARE @PrevJoined AS INT, @PrevUnsubscribed AS INT;

SET @PrevJoined = 0;

SET @PrevUnsubscribed = 0;

UPDATE #TempMonths
SET    @PrevJoined       = JoinedToDate       = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
FROM   #TempMonths WITH (INDEX (0), TABLOCKX)
OPTION (MAXDOP 1);

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, Joined AS JoinedThisMonth, ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers
FROM   #TempMonths;

Ok this is my final one:

-- Matt 1b
DECLARE @TempData TABLE (Joined [smallint], Unsubscribed [smallint], Num int, UNIQUE (Joined, Unsubscribed))
INSERT INTO @TempData (Joined, Unsubscribed, Num)
SELECT Joined, Unsubscribed, COUNT(*) FROM
 (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined,
         DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed
    FROM registrations WITH (TABLOCKX)) idat
GROUP BY [Joined], [Unsubscribed]

CREATE TABLE #TempMonths (MonthNumber [smallint], Joined [int], Unsubscribed [int], JoinedToDate [int], UnsubscribedToDate [int])
CREATE CLUSTERED INDEX [IX_TempMonths_1] ON #TempMonths (MonthNumber)

INSERT INTO #TempMonths (MonthNumber, Joined, Unsubscribed)
SELECT td1.Joined, td1.Num, td2.Num
FROM   (SELECT Joined, SUM(Num) AS Num FROM @TempData
        GROUP BY Joined) AS td1
       LEFT OUTER JOIN
       (SELECT Unsubscribed, SUM(Num) AS Num FROM @TempData
        GROUP BY Unsubscribed) AS td2
       ON td2.Unsubscribed = td1.Joined;

DECLARE @PrevJoined [int], @PrevUnsubscribed [int]
SET @PrevJoined = 0
SET @PrevUnsubscribed = 0

UPDATE #TempMonths
   SET @PrevJoined = JoinedToDate = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
  FROM #TempMonths WITH(INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, 
       Joined AS JoinedThisMonth, 
       ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, 
       JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers 
  FROM #TempMonths

Have used the 'quirky' update, similar to Matt in order to calculate the running totals, but generated my base data differently.

SQL Server suggested an index on DateLeft, but the cost of creating it over the impact on the execution time, meant that it was better left out. Have commented out the temporary table DROP at the end, just to save some CPU, but you will need it to keep running the script.

create table #MonthlyReport (ReportMonth datetime, NumJoined int, NumLeft int, MonthlyTotal int)
create clustered index idx_reportmonth on #MonthlyReport(ReportMonth)
insert into #MonthlyReport
select
    coalesce(subscribed.MonthJoined,unsubscribed.MonthLeft),
    subscribed.num,
    isnull(unsubscribed.num,0),
    0
from
    (
    	select 
    		datejoined - (datepart(dd, datejoined)-1) as MonthJoined,
    		count(*) as num
    	from Registrations
    	group by 
    		datejoined - (datepart(dd, datejoined)-1)
    ) subscribed
left join
    (
    	select 
    		dateleft - (datepart(dd, dateleft)-1) as MonthLeft,
    		count(*) as num
    	from Registrations
    	where dateleft is not null
    	group by 
    		dateleft - (datepart(dd, dateleft)-1)
    ) unsubscribed
on subscribed.MonthJoined = unsubscribed.MonthLeft

declare @runningtotal int
set @runningtotal = 0

update #MonthlyReport
set
    @runningtotal = MonthlyTotal = NumJoined - NumLeft + @runningtotal

select
    ReportMonth, NumJoined, NumLeft, MonthlyTotal
from #MonthlyReport
order by ReportMonth
--drop table #MonthlyReport

Here's my answer....

WITH MonthlySummaries([Date], joined, [left])--a table collected from two aggregations, giving us
      AS (SELECT    joined.[date],--those who joined and those who left in each month
                    joined.joined,
                    COALESCE([left].[left], 0)
          FROM      (SELECT CAST('01 '+LEFT(DATENAME(month, DateJoined), 3)
                            +' '+DATENAME(year, DateJoined) AS DATETIME) AS [Date],
                            COUNT(*) AS [joined]
                     FROM   Registrations
                     GROUP BY CAST('01 '+LEFT(DATENAME(month, DateJoined),
                                              3)+' '+DATENAME(year, DateJoined) AS DATETIME)
                    ) joined
                    LEFT OUTER JOIN (SELECT COUNT(*) AS [left],
                                            [date] = CAST('01 '
                                            +LEFT(DATENAME(month, Dateleft),
                                                  3)+' '+DATENAME(year, Dateleft) AS DATETIME)
                                     FROM   Registrations
                                     GROUP BY CAST('01 '
                                            +LEFT(DATENAME(month, Dateleft),
                                                  3)+' '+DATENAME(year, Dateleft) AS DATETIME)
                                    ) [left] ON joined.date=[left].date
         )
SELECT  MonthlySummaries.date,
        [joined],
        [left],
        c.subscribers
FROM    MonthlySummaries
        INNER JOIN (SELECT  a.date,
                            SUM(b.joined)-SUM(b.[left]) AS subscribers
                    FROM    MonthlySummaries a
                            INNER JOIN Monthlysummaries b ON b.date<=a.date
                    GROUP BY a.date
                   ) c ON c.date=MonthlySummaries.date
ORDER BY monthlySummaries.date

Oh well, I'll throw in a bone too. Phil, are you continously going to update us of the timings on your machine? This code allows you to enter any month to see what the status was then. The code also includes months were there are no new subscribers, just in case. And no quirky update of any sort.

DECLARE @Viewpoint INT

SET     @ViewPoint = DATEDIFF(MONTH, 0, GETDATE())

SELECT      DATEADD(MONTH, v.Number + d. Joined, 0) AS theMonth,
        SUM(CASE WHEN v.Number = 0 THEN d.NewSubscribers ELSE 0 END) AS NewSubscribes,
        SUM(CASE v.Number + d. Joined WHEN d.Leave THEN d.NewSubscribers ELSE 0 END) AS Unsubscribes,
        SUM(d.NewSubscribers) - SUM(CASE v.Number + d.Joined WHEN d.Leave THEN d.NewSubscribers ELSE 0 END) AS CurrentSubscribers
FROM        (
            SELECT      DATEDIFF(MONTH, 0, DateJoined) AS Joined,
                        CASE
                            WHEN DATEDIFF(MONTH, 0, DateLeft) < @ViewPoint THEN DATEDIFF(MONTH, 0, DateLeft)
                            ELSE @ViewPoint
                        END AS Leave,
                        COUNT(*) AS NewSubscribers
            FROM        dbo.Registrations
            WHERE       DateJoined < DATEADD(MONTH, @ViewPoint, 0)
            GROUP BY    DATEDIFF(MONTH, 0, DateJoined),
                        CASE
                            WHEN DATEDIFF(MONTH, 0, DateLeft) < @ViewPoint THEN DATEDIFF(MONTH, 0, DateLeft)
                            ELSE @ViewPoint
                        END
        ) AS d
INNER JOIN  master..spt_values AS v ON v.Type = 'P'
            AND v.Number <= d.Leave - d.Joined
WHERE       v.Number + d.Joined < @Viewpoint
GROUP BY    v.Number + d.Joined
ORDER BY    v.Number + d.Joined

Thank you for fixing the formatting!

I'm using the "Quirky XML Select" for this:

DECLARE @monthly_joins int
DECLARE @monthly_leaves int
DECLARE @running_users int
DECLARE @current_month date
DECLARE @xchar nvarchar(max)
DECLARE @xml XML
DECLARE @now date

SELECT @xchar = '', @monthly_joins = 0, @monthly_leaves = 0, @current_month = NULL, @running_users = 0, @now = '20091001'

SELECT 
    @xchar = 
    	CASE 
    		WHEN eventDate = ISNULL(@current_month, eventDate) 
    			THEN @xchar 
    		ELSE 
    			CASE
    				WHEN @current_month < @now 
    					THEN @xchar + '<A MONTH="'+ CONVERT(char(8),ISNULL(@current_month,eventDate),112) +'" JOINS="'+ CAST(@monthly_joins AS varchar(10)) +'" LEAVES="'+ CAST(@monthly_leaves AS varchar(10)) +'" TOT="'+ CAST(@running_users AS varchar(10)) +'"/>' 
    					ELSE @xchar
    				END
    	END,
    @monthly_joins = 
    	CASE 
    		WHEN eventDate = ISNULL(@current_month,eventDate) 
    			THEN @monthly_joins + JOINS 
    		ELSE JOINS 
    	END,
    @monthly_leaves = 
    	CASE 
    		WHEN eventDate = ISNULL(@current_month,eventDate) 
    			THEN @monthly_leaves + LEAVES
    		ELSE LEAVES 
    	END,
    @running_users = @running_users + JOINS - LEAVES,
    @current_month = eventDate
FROM (
    	SELECT CAST(CONVERT(char(6), DateJoined, 112) + '01' AS DATE) AS eventDate, JOINS = 1, LEAVES = 0
    	FROM registrations
    	UNION ALL
    	SELECT CAST(CONVERT(char(6), DateLeft, 112) + '01' AS DATE) AS eventDate, JOINS = 0, LEAVES = CASE WHEN DateLeft IS NOT NULL THEN 1 ELSE 0 END
    	FROM registrations
) AS Data
ORDER BY eventDate

IF @current_month < @now
    SELECT @xchar = @xchar + '<A MONTH="'+ CONVERT(char(8), @current_month, 112) +'" JOINS="'+ CAST(@monthly_joins AS varchar(10)) +'" LEAVES="'+ CAST(@monthly_leaves AS varchar(10)) +'" TOT="'+ CAST(@running_users AS varchar(10)) +'"/>'
SELECT @xml = CAST('<R>' + @xchar + '</R>' AS XML)


SELECT DISTINCT CONVERT(date, T.results.value('@MONTH[1]','char(6)') + '01', 112) as [month],
    T.results.value('@JOINS[1]','int') as joins,
    T.results.value('@LEAVES[1]','int') as leaves,
    T.results.value('@TOT[1]','int') as tot
FROM @xml.nodes('/R/A') AS T(results)

There's still some index needed, but I'm going with this for the moment. I'll be back with indexes maybe.

I almost didn't post this when I saw how similar it is to some of the other answers. It's pretty fast and really simple (I thought) so here it goes...

DECLARE @Count INT
DECLARE @data TABLE
(
    RptDate DATETIME PRIMARY KEY,
    JoinCount INT,
    LeaveCount INT,
    SubscriberCount INT
)

SET @Count = 0

INSERT @data
(
    RptDate,
    JoinCount,
    LeaveCount
)
SELECT
    joins.JoinMonth AS RptDate,
    joins.JoinCount,
    ISNULL(leaves.LeaveCount, 0)
FROM
    (
    SELECT
    	r.DateJoined + 1 - DATEPART(dd, r.DateJoined) AS JoinMonth,
    	COUNT(*) AS JoinCount
    FROM
    	dbo.Registrations r
    GROUP BY
    	r.DateJoined + 1 - DATEPART(dd, r.DateJoined)
    ) AS joins
LEFT OUTER JOIN
    (
    SELECT
    	r.DateLeft + 1 - DATEPART(dd, r.DateLeft) AS LeaveMonth,
    	COUNT(*) AS LeaveCount
    FROM
    	dbo.Registrations r
    WHERE
    	r.DateLeft IS NOT NULL
    GROUP BY
    	r.DateLeft + 1 - DATEPART(dd, r.DateLeft)
    ) AS leaves
ON
    joins.JoinMonth = leaves.LeaveMonth

UPDATE
    d
SET
    @Count = d.SubscriberCount = d.JoinCount - d.LeaveCount + @Count
FROM
    @data d

SELECT
    d.RptDate,
    d.JoinCount,
    d.LeaveCount,
    d.SubscriberCount
FROM
    @data d

Ideally solution would use window aggregates with a RANGE partition to calculate all of the aggregates in a single pass over the data. Unfortunately this is not currently supported in SQL Server…

In the following solution temp tables could be subqueries but subqueries cause SQL Server to choke. The speed of this solution is dependent on the performance of the TEMP database. A nearly full or badly fragmented TEMP db file will slow things down.

--Create temp table of months, assuming data contains all possible months...
SELECT DISTINCT ReportMonth
  INTO #months
  FROM (
SELECT DISTINCT DATEADD(d, -(DatePart(DAY,DateJoined)-1), DateJoined) ReportMonth FROM  Registrations --Adds
 UNION
SELECT DISTINCT DATEADD(d, -(DatePart(DAY,DateLeft)-1), DateLeft) ReportMonth FROM  Registrations --Drops
 ) a
 WHERE ReportMonth IS NOT NULL

--Create a temp table for Adds, summarised by month
SELECT DATEADD(d, -(DatePart(DAY,DateJoined)-1), DateJoined) ReportMonth
      ,COUNT([Registration_ID]) Adds
  INTO  #adds
  FROM  Registrations
 GROUP BY DATEADD(d, -(DatePart(DAY,DateJoined)-1), DateJoined)

--Create a temp table for Drops, summarised by month
SELECT DATEADD(d, -(DatePart(DAY,DateLeft)-1), DateLeft)   DropMonth
       ,COUNT([Registration_ID]) Drops
  INTO  #drops
  FROM  Registrations
 WHERE  DateLeft IS NOT NULL
 GROUP BY DATEADD(d, -(DatePart(DAY,DateLeft)-1),   DateLeft)

--Join all 3 temp tables together and
SELECT m.ReportMonth
      ,ISNULL(a.Adds,0) SubsAdded
      ,ISNULL(d.Drops,0) SubsDropped
--Use 2 correlated subqueries to calculate the running total.
      ,(SELECT SUM(Adds) FROM #adds WHERE #adds.ReportMonth <= a.ReportMonth)
      - ISNULL((SELECT SUM(Drops) FROM #drops WHERE #drops.DropMonth <= a.ReportMonth),0) TotalSubs
  FROM #months m
  LEFT JOIN #adds a
    	 ON m.ReportMonth = a.ReportMonth
  LEFT JOIN #drops d
         ON a.ReportMonth = d.DropMonth
ORDER BY ReportMonth

  SELECT Dates.date AS [date],
    (SELECT COUNT(*) FROM registrations r WHERE DateAdd(second, -1,DateAdd(month,1,Dates.date)) 
    	BETWEEN r.dateJoined AND COALESCE(r.DateLeft,GETDATE() )) AS subscribers,
    (SELECT COUNT(*) FROM registrations r WHERE datepart (month,r.dateJoined) =datepart (month,Dates.date)
    				and datepart (year,r.dateJoined) =datepart (year,Dates.date)) AS joined,
    (SELECT COUNT(*) FROM registrations r WHERE datepart (month,r.dateleft) =datepart (month,Dates.date)
    				and datepart (year,r.dateLeft) =datepart (year,Dates.date)) AS [left]

FROM
    (SELECT 
      [date]=CAST('01 '+LEFT(DATENAME(month,DateJoined),3)+' '+DATENAME(year,DateJoined) AS DATETIME)
    FROM Registrations
    GROUP BY CAST('01 '+LEFT(DATENAME(month,DateJoined),3)+' '+DATENAME(year,DateJoined) AS DATETIME)) dates
ORDER BY date