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;
