Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ideas for query

Ideas for query

From
Steve Clark
Date:
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.clark@netwolves.com
http://www.netwolves.com

Re: Ideas for query

From
Raymond O'Donnell
Date:
On 13/10/2011 12:17, Steve Clark wrote:
> 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.

The way I'd approach this is to do a LEFT OUTER JOIN between the units
table and the events table, with the units on the left of the join: this
way any particular unit will always appear in the result set, and if
there are no corresponding rows in the events table then you know that
the unit had 100% uptime.

HTH.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: Ideas for query

From
Steve Clark
Date:
On 10/13/2011 08:31 AM, Raymond O'Donnell wrote: 
On 13/10/2011 12:17, Steve Clark wrote:

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.

The way I'd approach this is to do a LEFT OUTER JOIN between the units
table and the events table, with the units on the left of the join: this
way any particular unit will always appear in the result set, and if
there are no corresponding rows in the events table then you know that
the unit had 100% uptime.

HTH.

Ray.
Hi Ray,

Thanks for the response, I am afraid I don't know enough on how to formulate the left outer join
so I have attacked the problem from a different direction. Creating a temporary table with all
the units set to 100% then running my existing query and using the results to update my
temporary table where the unit serial no's match.

Steve

--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com