If most of the validity intervals for your membership are longer than a few days, take a look at Martin Smith's answer. This approach will probably be faster.

When you grab the calendar table ( DIM.[Date]

) and join it to the left Memberships

, you can complete the table scan Memberships

for each date in the range. Even if (ValidFromDate, ValidToDate)

there is no index, it might not be very useful.

It is easy to rotate it. Scan the table Memberships

just once and for each membership find the 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';

      

        
        
        
      

    

Request 1

SELECT
    CA.dt
    ,COUNT(*) AS MembershipCount
FROM
    @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 CA
GROUP BY
    CA.dt
ORDER BY
    CA.dt
OPTION(RECOMPILE);

      

        
        
        
      

    

OPTION(RECOMPILE)

not really required, I include it in all queries when I compare execution plans to make sure I get the latest plan when I play with the queries.

When I looked at the plan for this query, I saw that the table lookup Calendar.dt

was only used ValidFromDate

and ValidToDate

, @RangeFrom

and tags @RangeTo

were moved to the remainder predicate. It's not perfect. The optimizer is not smart enough to calculate the maximum of two dates ( ValidFromDate

and @RangeFrom

) and use that date as the starting point of the search.

It is easy to help the optimizer:

Request 2

SELECT
    CA.dt
    ,COUNT(*) AS MembershipCount
FROM
    @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 CA
GROUP BY
    CA.dt
ORDER BY
    CA.dt
OPTION(RECOMPILE)
;

      

        
        
        
      

    

In this query, the search is optimal and does not read dates that might be discarded later.

Finally, you may not need to scan the entire table Memberships

. We only need rows where the given date range overlaps with the valid membership range.

Request 3

SELECT
    CA.dt
    ,COUNT(*) AS MembershipCount
FROM
    @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 CA
WHERE
    Memberships.ValidToDate >= @RangeFrom
    AND Memberships.ValidFromDate <= @RangeTo
GROUP BY
    CA.dt
ORDER BY
    CA.dt
OPTION(RECOMPILE)
;

      

        
        
        
      

    

Two intervals [a1;a2]

and [b1;b2]

intersect when

a2 >= b1 and a1 <= b2

      

        
        
        
      

    

These queries assume that the table Calendar

has an index on dt

.

You should try and see which indexes are best for the table Memberships

. For the last query, if the table is fairly large, it is likely that two separate indexes on ValidFromDate

and on ValidToDate

are better than one index on (ValidFromDate, ValidToDate)

.

You should try different queries and measure their performance on real hardware with real data. Performance can depend on the distribution of the data, the number of memberships in it, what their actual dates are, how wide or narrow the range is, etc.

I recommend using an excellent tool called SQL Sentry Plan Explorer to analyze and compare execution plans. It's free. It shows a lot of useful statistics like execution time and number of reads for each request. The screenshots above are from this tool.