Aggregate for each day over time series, without using non-equijoin logic

If most of your membership validity intervals are longer than few days, have a look at an answer by Martin Smith. That approach is likely to be faster.

When you take calendar table (DIM.[Date]) and left join it with Memberships, you may end up scanning the Memberships table for each date of the range. Even if there is an index on (ValidFromDate, ValidToDate), it may not be super useful.

It is easy to turn it around.Scan the Memberships table only once and for each membership find those dates that are valid using CROSS APPLY.

Sample data

DECLARE @T TABLE (MembershipId int, ValidFromDate date, ValidToDate date);INSERT INTO @T VALUES(1, '1997-01-01', '2006-05-09'),(2, '1997-01-01', '2017-05-12'),(3, '2005-06-02', '2009-02-07');DECLARE @RangeFrom date = '2006-01-01';DECLARE @RangeTo   date = '2006-12-31';

Query 1

SELECT    CA.dt    ,COUNT(*) AS MembershipCountFROM    @T AS Memberships    CROSS APPLY    (        SELECT dbo.Calendar.dt        FROM dbo.Calendar        WHERE            dbo.Calendar.dt >= Memberships.ValidFromDate            AND dbo.Calendar.dt <= Memberships.ValidToDate            AND dbo.Calendar.dt >= @RangeFrom            AND dbo.Calendar.dt <= @RangeTo    ) AS CAGROUP BY    CA.dtORDER BY    CA.dtOPTION(RECOMPILE);

OPTION(RECOMPILE) is not really needed, I include it in all queries when I compare execution plans to be sure that I'm getting the latest plan when I play with the queries.

When I looked at the plan of this query I saw that the seek in the Calendar.dt table was using only ValidFromDate and ValidToDate, the @RangeFrom and @RangeTo were pushed to the residue predicate. It is not ideal. The optimiser is not smart enough to calculate maximum of two dates (ValidFromDate and @RangeFrom) and use that date as a starting point of the seek.

It is easy to help the optimiser:

Query 2

SELECT    CA.dt    ,COUNT(*) AS MembershipCountFROM    @T AS Memberships    CROSS APPLY    (        SELECT dbo.Calendar.dt        FROM dbo.Calendar        WHERE            dbo.Calendar.dt >=                 CASE WHEN Memberships.ValidFromDate > @RangeFrom                 THEN Memberships.ValidFromDate                 ELSE @RangeFrom END            AND dbo.Calendar.dt <=                 CASE WHEN Memberships.ValidToDate < @RangeTo                 THEN Memberships.ValidToDate                 ELSE @RangeTo END    ) AS CAGROUP BY    CA.dtORDER BY    CA.dtOPTION(RECOMPILE);

In this query the seek is optimal and doesn't read dates that may be discarded later.

Finally, you may not need to scan the whole Memberships table.We need only those rows where the given range of dates intersects with the valid range of the membership.

Query 3

SELECT    CA.dt    ,COUNT(*) AS MembershipCountFROM    @T AS Memberships    CROSS APPLY    (        SELECT dbo.Calendar.dt        FROM dbo.Calendar        WHERE            dbo.Calendar.dt >=                 CASE WHEN Memberships.ValidFromDate > @RangeFrom                 THEN Memberships.ValidFromDate                 ELSE @RangeFrom END            AND dbo.Calendar.dt <=                 CASE WHEN Memberships.ValidToDate < @RangeTo                 THEN Memberships.ValidToDate                 ELSE @RangeTo END    ) AS CAWHERE    Memberships.ValidToDate >= @RangeFrom    AND Memberships.ValidFromDate <= @RangeToGROUP BY    CA.dtORDER BY    CA.dtOPTION(RECOMPILE);

Two intervals [a1;a2] and [b1;b2] intersect when

a2 >= b1 and a1 <= b2

These queries assume that Calendar table has an index on dt.

You should try and see what indexes are better for the Memberships table.For the last query, if the table is rather large, most likely two separate indexes on ValidFromDate and on ValidToDate would be better than one index on (ValidFromDate, ValidToDate).

You should try different queries and measure their performance on the real hardware with real data. Performance may depend on the data distribution, how many memberships there are, what are their valid dates, how wide or narrow is the given range, etc.

I recommend to use a great tool called SQL Sentry Plan Explorer to analyse and compare execution plans. It is free. It shows a lot of useful stats, such as execution time and number of reads for each query. The screenshots above are from this tool.

On the assumption your date dimension contains all dates contained in all membership periods you can use something like the following.

The join is an equi join so can use hash join or merge join not just nested loops (which will execute the inside sub tree once for each outer row).

Assuming index on (ValidToDate) include(ValidFromDate) or reverse this can use a single seek against Memberships and a single scan of the date dimension. The below has an elapsed time of less than a second for me to return the results for a year against a table with 3.2 million members and general active membership of 1.4 million (script)

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 MembershipCountFROM   MCWHERE  DateKey BETWEEN @StartDate AND @EndDate ORDER BY DateKey

Demo (uses extended period as the calendar year of 2016 isn't very interesting with the example data)

One approach is to first use an INNER JOIN to find the set of matches and COUNT() to project MemberCount GROUPed BY DateKey, then UNION ALL with the same set of dates, with a 0 on that projection for the count of members for each date. The last step is to SUM() the MemberCount of this union, and GROUP BY DateKey. As requested, this avoids LEFT JOIN and NOT EXISTS. As another member pointed out, this is not an equi-join, because we need to use a range, but I think it does what you intend.

This will serve up 1 year's worth of data with around 100k logical reads. On an ordinary laptop with a spinning disk, from cold cache, it serves 1 month in under a second (with correct counts).

Here is an example that creates 3.3 million rows of random duration. The query at the bottom returns one month's worth of data.

--Stay quiet for a momentSET NOCOUNT ONSET STATISTICS IO OFFSET STATISTICS TIME OFF--Clean up if re-runningDROP TABLE IF EXISTS DIM_DATEDROP TABLE IF EXISTS FACT_MEMBER--Date dimensionCREATE TABLE DIM_DATE  (  DateKey DATE NOT NULL   )--Membership factCREATE TABLE FACT_MEMBER  (  MembershipId INT NOT NULL  , ValidFromDateKey DATE NOT NULL  , ValidToDateKey DATE NOT NULL  )--Populate Date dimension from 2001 through end of 2018DECLARE @startDate DATE = '2001-01-01'DECLARE @endDate DATE = '2018-12-31';WITH CTE_DATE AS(SELECT @startDate AS DateKeyUNION ALLSELECT       DATEADD(DAY, 1, DateKey)FROM       CTE_DATE AS DWHERE       D.DateKey < @endDate)INSERT INTO  DIM_DATE  (  DateKey  )SELECT  D.DateKeyFROM  CTE_DATE AS DOPTION (MAXRECURSION 32767)--Populate Membership fact with members having a random membership length from 1 to 36 months ;WITH CTE_DATE AS(SELECT @startDate AS DateKeyUNION ALLSELECT       DATEADD(DAY, 1, DateKey)FROM       CTE_DATE AS DWHERE       D.DateKey < @endDate),CTE_MEMBER AS(SELECT 1 AS MembershipIdUNION ALLSELECT MembershipId + 1 FROM CTE_MEMBER WHERE MembershipId < 500),CTE_MEMBERSHIPAS(SELECT  ROW_NUMBER() OVER (ORDER BY NEWID()) AS MembershipId  , D.DateKey AS ValidFromDateKeyFROM  CTE_DATE AS D  CROSS JOIN CTE_MEMBER AS M)INSERT INTO    FACT_MEMBER    (    MembershipId    , ValidFromDateKey    , ValidToDateKey    )SELECT    M.MembershipId    , M.ValidFromDateKey      , DATEADD(MONTH, FLOOR(RAND(CHECKSUM(NEWID())) * (36-1)+1), M.ValidFromDateKey) AS ValidToDateKeyFROM    CTE_MEMBERSHIP AS MOPTION (MAXRECURSION 32767)--Add clustered Primary Key to Date dimensionALTER TABLE DIM_DATE ADD CONSTRAINT PK_DATE PRIMARY KEY CLUSTERED    (    DateKey ASC    )--Index--(Optimize in your spare time)DROP INDEX IF EXISTS SK_FACT_MEMBER ON FACT_MEMBERCREATE CLUSTERED INDEX SK_FACT_MEMBER ON FACT_MEMBER    (    ValidFromDateKey ASC    , ValidToDateKey ASC    , MembershipId ASC    )RETURN--Start test--Emit statsSET STATISTICS IO ONSET STATISTICS TIME ON--Establish range of datesDECLARE  @rangeStartDate DATE = '2010-01-01'  , @rangeEndDate DATE = '2010-01-31'--UNION the count of members for a specific date range with the "zero" set for the same range, and SUM() the counts;WITH CTE_MEMBERAS(SELECT    D.DateKey    , COUNT(*) AS MembershipCountFROM    DIM_DATE AS D    INNER JOIN FACT_MEMBER AS M ON        M.ValidFromDateKey <= @rangeEndDate        AND M.ValidToDateKey >= @rangeStartDate        AND D.DateKey BETWEEN M.ValidFromDateKey AND M.ValidToDateKeyWHERE    D.DateKey BETWEEN @rangeStartDate AND @rangeEndDateGROUP BY    D.DateKeyUNION ALLSELECT    D.DateKey    , 0 AS MembershipCountFROM    DIM_DATE AS DWHERE    D.DateKey BETWEEN @rangeStartDate AND @rangeEndDate)SELECT    M.DateKey    , SUM(M.MembershipCount) AS MembershipCountFROM    CTE_MEMBER AS MGROUP BY    M.DateKeyORDER BY    M.DateKey ASCOPTION (RECOMPILE, MAXDOP 1)