SQL Server Join – How to Join by Most Recent Preceding Date

If your priority is speed of selects, the following approach allows for extremely fast selects. Instead of storing a period, you want to store two events (period start and period end). Change column is 1 when the period begins, and is -1 when the period ends. If more than one event occurs on the same day, they must have different EventNumberPerDay. RunningTotal is the number of open peroids after the event has happened:

CREATE TABLE dbo.Events(
 PeriodId INT NOT NULL,
 Change SMALLINT NOT NULL,
 ChangeDate DATE NOT NULL,
 EventNumberPerDay INT NOT NULL,
 RunningTotal INT NOT NULL);
GO

INSERT dbo.Events
        ( PeriodId ,
          Change ,
          ChangeDate ,
          EventNumberPerDay,
          RunningTotal
        )
-- first period begins    
VALUES  ( 1 , 1, '20120801', 1, 1),
-- second period begins on the same day
(2, 1, '20120801', 2, 2),
-- third period begins
(3,1,'20120803',1, 3),
-- second period ends on the same day
(2,-1,'20120803',2, 2),
-- fourth period begins
(4,1,'20120804',1,3),
-- fourth period ends
(4,-1,'20120805',1,2),
-- first period ends
(1, -1, '20120808',1,  1),
-- third period ends
(3, -1, '20120809',1,  0);
GO

Also you need a calendar table:

CREATE TABLE dbo.Calendar([Date] DATE NOT NULL);
GO

INSERT INTO dbo.Calendar([Date])
VALUES('20120801'),
('20120802'),
('20120803'),
('20120804'),
('20120805'),
('20120806'),
('20120807'),
('20120808'),
('20120809'),
('20120810'),
('20120811');

Once that is accomplished, then your select is very simple and very fast:

SELECT  [Date] ,
        coalesce(RunningTotal, 0) AS NumOpenIntervals
FROM    dbo.Calendar
        OUTER APPLY ( SELECT TOP ( 1 )
                                RunningTotal
                      FROM      dbo.Events
                      WHERE     ChangeDate <= [Date]
                      ORDER BY  ChangeDate DESC, EventNumberPerDay DESC
                    ) AS t
ORDER BY [Date]

Of course, this query is only correct if the data in Events table is valid. We can use constraints to ensure 100% data integrity. I can explain how if you are interested.

Another alternative is to just load your raw data, your periods, into a client application - your problem is absolutely trivial in C++/C#/Java.

Yet another approach is to use an RDBMS with fast cursors such as Oracle - that will allow you to just write a simple cursor and enjoy good performance, but still not always as good as my first solution.

I think you want (tested at SQL-Fiddle-1):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(SUM(dt.the_count), 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
    LEFT JOIN 
        datatable AS dt
            ON  dt.the_date = ct.the_day
            AND dt.the_id = dd.the_id 
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
GROUP BY 
        dd.the_id, ct.the_day
ORDER BY 
        the_id, the_day ;

It can also be written like this (SQL-Fiddle-2):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(
            ( SELECT SUM(dt.the_count)
              FROM   datatable AS dt
              WHERE  dt.the_date = ct.the_day
                AND  dt.the_id = dd.the_id     
            )               
                            , 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
ORDER BY 
        the_id, the_day ;