Returning zero when requested time period has no data

Database: Access or SQL Server.

I get data from a meter at 15 minute intervals and want to report the data in hourly intervals. I hoped that an outer join between two tables would solve the problem. But it doesn't return values for all the time periods when there is no corresponding interval record.

Table 1: TimePeriod
FirstTime               LastTime
11/24/2001 10:00:00 PM  11/24/2001 11:00:00 PM
11/24/2001 11:00:00 PM  11/25/2001
11/25/2001              11/25/2001 1:00:00 AM

Table 2: IntervalData
M_MeterNo       IntDat_Time             IntDat_Kwh	
12341234        11/24/2001 9:45:00 PM   0.0531		
12341234        11/24/2001 10:00:00 PM  0.04485	
12341234        11/24/2001 10:15:00 PM  0.05395	
12341234        11/24/2001 10:30:00 PM  0.0458	
12341234        11/24/2001 10:45:00 PM  0.05065	
12341234        11/24/2001 11:00:00 PM  0.0484	
12341234        11/24/2001 11:15:00 PM  0.048	
12341234        11/24/2001 11:30:00 PM  0.05155	
12341234        11/24/2001 11:45:00 PM  0.0461	

Query:
SELECT t.FirstTime, t.LastTime, Sum(i.IntDat_Kwh) AS SumKwh
FROM  TimePeriod t LEFT OUTER JOIN IntervalData i
      ON ((t.FirstTime < i.IntDat_Time) AND (t.LastTime >=  i.IntDat_Time))
WHERE (i.M_MeterNo='12341234') 
GROUP BY t.FirstTime, t.LastTime
ORDER BY t.FirstTime

Response:
FirstTime               LastTime                SumKwh	
11/24/2001 10:00:00 PM  11/24/2001 11:00:00 PM  0.1988	
11/24/2001 11:00:00 PM  11/25/2001              0.14565	

The problem lies with your WHERE clause. If an outer join is employed, and no rows exist for the non-preserved table, then the columns are "padded" with nulls. The value for M_MeterNo for those particular result rows will be NULL, therefore (i.M_MeterNo='12341234') will not resolve to TRUE. The problem can be solved by moving the condition into the ON clause, as this effectively filters the rows before the join.

This concept may be more clear when illustrated with a simple example. Suppose you built a toll road. You record when cars pass through a particular toll booth, as well as the cars' colors. That table and its data look like this:

create table TollCars
( Passed datetime primary key,
  Color varchar(5) not null
);

insert into TollCars values ('03/01/02 14:00','RED');
insert into TollCars values ('03/01/02 16:00','BLUE');
insert into TollCars values ('03/01/02 20:00','GREEN');
insert into TollCars values ('03/02/02 07:00','BLUE');
insert into TollCars values ('03/03/02 13:00','BLUE');
insert into TollCars values ('03/03/02 14:00','GREEN');
insert into TollCars values ('03/04/02 10:00','RED');
insert into TollCars values ('03/04/02 22:00','GREEN');
insert into TollCars values ('03/06/02 11:00','BLUE');
insert into TollCars values ('03/07/02 17:00','RED');
insert into TollCars values ('03/07/02 19:00','RED');

create table Days
( Day datetime primary key
    constraint MakeSureDayHasNoTime
      check ( cast(Day as float) = round(cast(Day as float),0))
);

insert into Days values ('03/01/02');
insert into Days values ('03/02/02');
insert into Days values ('03/03/02');
insert into Days values ('03/04/02');
insert into Days values ('03/05/02');
insert into Days values ('03/06/02');
insert into Days values ('03/07/02');

select convert(varchar,Day,107) Day, 
       count(Passed) TotalCars
  from Days left join TollCars
    on TollCars.Passed >= Days.Day and 
       TollCars.Passed < Days.Day + 1
  group by Day;

Day          TotalCars
------------ ---------
Mar 01, 2002         3
Mar 02, 2002         1
Mar 03, 2002         2
Mar 04, 2002         2
Mar 05, 2002         0
Mar 06, 2002         1
Mar 07, 2002         2

This answer is continued.