From: Shane Ambler <pgsql@Sheeky.Biz>
To: Trilok Kumar <trilokumar@yahoo.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: sql query question ?
Date: Sun, 30 Dec 2007 03:52:05 +1030
Message-ID: <4776823D.30707@Sheeky.Biz> (raw)
In-Reply-To: <238306.65674.qm@web27208.mail.ukl.yahoo.com>
References: <238306.65674.qm@web27208.mail.ukl.yahoo.com>
Trilok Kumar wrote:
> Hi All,
>
> I have a table called
>
> vehicle_duty_cycle_summary
>
>
> vehicle_master_id | starting_odometer |
> ending_odometer | login_time |
> logout_time
> -------------------+-------------------+-----------------+----------------------------+----------------------------
>
> 4 | 53379.00 | 53504.00 | 2006-12-19
> 16:19:16.584547 | 2006-12-20 07:12:57.716907
>
> I would like to compute the following on this table.
>
> Idle time of vehicel=(ending_odometer reading of the
> previous day -
> starting_odometer reading of the present day) for
> every vehicle
I would think your naming may be confusing and may not be
implemented(recorded?) very well.
I think Idle Time is a misleading name by your explanation - Idle time
would be defined as (logout_time - previous login_time) which gives you
the time the vehicle was sitting in the garage.
What you want may be better called unmetered_travel and would be the
distance traveled between login_time and logout_time
This would simply be
select vehicle_master_id,
(ending_odometer - starting_odometer) as unmetered_travel
from vehicle_duty_cycle_summary;
Going by the naming you have used it would appear that you are recording
the time spent in the garage (going by the data you have shown I would
say this is a company car garage not a repair shop)
One record would appear to record the time the car is in the garage -
login_time would be the time the employee returned the car and
logout_time would be when the car next went out to someone.
I would think you want the opposite of that - the time and odometer
reading when an employee takes the car and the time and odometer of when
it is returned and the employee_id of who had it. This will give you who
used the car at what time and what distances they travelled (which of
course would be work related travel)
Going with those changes -
The distance traveled by an employee is easy to workout, if you wanted
to workout the unmetered (non-work) distance traveled you could try
something like (untested) -
select
v1.vehicle_master_id
, v1.starting_odometer -
(select v2.ending_odometer
from vehicle_duty_cycle_summary v2
where v2.vehicle_master_id = v1.vehicle_master_id
and v2.login_time < v1.logout_time
order by v2.login_time desc limit 1)
as unmetered_travel
from vehicle_duty_cycle_summary v1
where v1.vehicle_master_id = 4;
I would calculate idle time as -
select
v1.vehicle_master_id
, v1.logout_time -
(select v2.login_time
from vehicle_duty_cycle_summary v2
where v2.vehicle_master_id = v1.vehicle_master_id
and v2.login_time < v1.logout_time
order by v2.login_time desc limit 1)
as unmetered_travel
from vehicle_duty_cycle_summary v1
where v1.vehicle_master_id = 4;
If this isn't the way it should work you should be able to adapt the
query to match your definition of idle time.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@Sheeky.Biz, trilokumar@yahoo.co.uk
Subject: Re: sql query question ?
In-Reply-To: <4776823D.30707@Sheeky.Biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox