/***************************************************** Peso 4e - 20091017 *******************************************************/ --A CREATE TABLE #Stage ( theMonth SMALLINT NOT NULL , PeopleJoined INT NOT NULL , PeopleLeft INT NOT NULL , Subscribers INT NOT NULL ) --B INSERT #Stage ( theMonth , PeopleJoined , PeopleLeft , Subscribers ) --C SELECT u.theMonth , sum(case WHEN u.theCol = 'DateJoined' THEN u.Registrations ELSE 0 END) AS PeopleJoined , sum(case WHEN u.theCol = 'DateLeft' THEN u.Registrations ELSE 0 END) AS PeopleLeft , 0 AS Subscribers --D FROM ( --E SELECT datediff(MONTH, 0, DateJoined) AS DateJoined , datediff(MONTH, 0, DateLeft) AS DateLeft , count(*) AS Registrations FROM dbo.Registrations GROUP BY datediff(MONTH, 0, DateJoined) , datediff(MONTH, 0, DateLeft) --F ) AS d UNPIVOT ( theMonth FOR theCol IN ( d.DateJoined,d.DateLeft ) ) AS u --G GROUP BY u.theMonth --H HAVING sum(case WHEN u.theCol = 'DateJoined' THEN u.Registrations ELSE 0 END) > 0 --I DECLARE @Subscribers INT = 0 ; --J ; WITH Yak ( theMonth, PeopleJoined, PeopleLeft, Subscribers ) AS ( --K SELECT TOP 2147483647 dateadd(MONTH, theMonth, 0) AS theMonth , PeopleJoined , PeopleLeft , Subscribers FROM #Stage ORDER BY theMonth --L ) --M UPDATE Yak SET @Subscribers = Subscribers = @Subscribers + PeopleJoined - PeopleLeft --N OUTPUT inserted.theMonth , inserted.PeopleJoined , inserted.PeopleLeft , inserted.Subscribers --O DROP TABLE #stage --P