Ideas for query

Hello List,

I am a not very experienced writing sql and I have a problem I can't readily solve, so
I was hoping to get some help from this great list.

Here is my problem I have a table that has event data about the status of units in the field. It has
many kinds of events one of which has down time information. I have written a query to extract that
information and calculate the % downtime. The problem I am having is that if the unit was never down
I don't see it in my query, I would like to be able to somehow report it as 100% up.

Below is an example of the event data and my query is below it. Any suggestions would be greatly appreciated.
t_unit has base information about the unit

v_unit_event_info
group_id | unit_serial_no | event_category | event_mesg | event_date
----------+----------------+----------------+-------------------------------------------------------------------------------------------------+-------------------------------
146 | K101334 | UNIT | Unit is Up. Last packet received:2010-12-10 22:56:18.330648-05, Total down time:00:09:17.757409 | 2010-12-10 22:56:18.330648-05

select unit_serial_no,
sum(down_time),
round((100-(extract(epoch from sum(down_time))/extract(epoch from (timestamp '2011-09-31 23:59:59' - timestamp '2011-09-01 00:00:00')::interval))*100)::numeric,2) as "Up Time %"
from (select unit_serial_no, down_time
from (select unit_serial_no, substring(event_mesg from
strpos(event_mesg,'Total')+16 for 40)::interval as down_time
from v_unit_event_info where event_category='UNIT'
and event_mesg like '%Total %'
and event_date >= '2011-09-01 00:00:00'
and event_date <= '2011-09-30 23:59:59' and unit_serial_no in
(select unit_serial_no from t_unit where group_id='199' and activated='y')
order by unit_serial_no) as foo where down_time < '5 days')
as foo2 group by unit_serial_no;
--
Stephen Clark
*NetWolves*
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve(dot)clark(at)netwolves(dot)com
http://www.netwolves.com