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: [email protected]
http://www.netwolves.com
