--BEGIN DATA SETUP
SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
GO

DROP TABLE IF EXISTS Memberships
DROP TABLE IF EXISTS DIM_DATE


CREATE TABLE Memberships
  (
     [MembershipId]  INT IDENTITY PRIMARY KEY,
     [ValidFromDate] DATETIME,
     [ValidToDate]   DATETIME,
     CHECK ([ValidToDate] >= [ValidFromDate])
  );


WITH Nums AS
(
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@SPID) - 1 AS Number
FROM master..spt_values v1, master..spt_values v2
)
INSERT INTO Memberships
            ([ValidFromDate],
             [ValidToDate])

SELECT DATEADD(DAY, N1.Number, '1997-01-01'),
       DATEADD(DAY, N1.Number + CRYPT_GEN_RANDOM(3)% 7500, '1997-01-01')
FROM Nums N1
CROSS JOIN Nums N2
WHERE N1.Number < 8000 AND N2.Number < 400
ORDER BY N1.Number

---Also evaluate the reverse index of this - I'm assuming ignoring expired members will
--generally be more useful
CREATE INDEX ix ON Memberships (ValidToDate) include([ValidFromDate]);

CREATE TABLE DIM_DATE (DateKey DATE PRIMARY KEY);

WITH CTE_DATE
AS (SELECT CAST('1997-01-01' AS DATE) AS DateKey
         UNION ALL
         SELECT DATEADD(DAY, 1, DateKey)
         FROM   CTE_DATE AS D
         WHERE  D.DateKey < '2039-06-09')
INSERT INTO DIM_DATE
            (DateKey)
SELECT D.DateKey
FROM   CTE_DATE AS D
OPTION (MAXRECURSION 32767);

--END DATA SETUP
SET STATISTICS IO, TIME ON;

    DECLARE @StartDate DATE = '2016-01-01',
            @EndDate   DATE = '2016-12-31';

    WITH MD
         AS (SELECT Date,
                    SUM(Adj) AS MemberDelta
             FROM   Memberships
                    CROSS APPLY (VALUES ( ValidFromDate, +1),
                                        --Membership count decremented day after the ValidToDate
                                        (DATEADD(DAY, 1, ValidToDate), -1) ) V(Date, Adj)
             WHERE
              --Members already expired before the time range of interest can be ignored
              ValidToDate >= @StartDate
              AND
              --Members whose membership starts after the time range of interest can be ignored
              ValidFromDate <= @EndDate
             GROUP  BY Date),
         MC
         AS (SELECT DD.DateKey,
                    SUM(MemberDelta) OVER (ORDER BY DD.DateKey ROWS UNBOUNDED PRECEDING) AS CountOfNonIgnoredMembers
             FROM   DIM_DATE DD
                    LEFT JOIN MD
                      ON MD.Date = DD.DateKey)
    SELECT DateKey,
           CountOfNonIgnoredMembers AS MembershipCount
    FROM   MC
    WHERE  DateKey BETWEEN @StartDate AND @EndDate