Returning zero when requested time period has no data
Database: Access or SQL Server.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.14565How can I get the query to return a zero when the time period I am requesting does not have a corresponding interval data record?
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 2This 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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments