Ask the Expert

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	
How can I get the query to return a zero when the time period I am requesting does not have a corresponding interval data record?


    Requires Free Membership to View

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');
For reporting purposes, you also have a table of Days, like this:
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');
When you outer join the TollCars to Days, you are able to see totals for every day in the Days table, including the days for which no cars have passed the toll booth. March 5th is such a day. The SQL and the resulting rows look like this:
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 looks great, however, things get a bit more complicated when you want to see the same breakdown by day, but for red cars only.

This answer is continued.


This was first published in March 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: