Thread: sql query question ?
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 4 | 51310.00 |
51457.00 | 2006-12-05 16:04:51.585441 | 2006-12-06
07:18:10.251535 4 | 42411.00 |
42411.00 | 2006-09-14 16:03:49.541442 | 2006-09-15
08:24:41.308339 4 | 54012.00 |
54146.00 | 2006-12-25 16:14:31.313728 | 2006-12-26
07:05:55.82487 4 | 43071.00 |
43181.00 | 2006-09-21 11:24:43.466766 | 2006-09-22
07:10:09.362792 4 | 51208.00 |
51305.00 | 2006-12-04 16:14:14.600105 | 2006-12-05
08:03:38.139832 4 | 50235.00 |
50370.00 | 2006-11-27 15:55:48.866925 | 2006-11-28
07:27:25.898991 4 | 53231.00 |
53362.00 | 2006-12-18 16:06:42.764133 | 2006-12-19
07:06:03.450072 4 | 52656.00 |
52818.00 | 2006-12-13 16:03:47.189303 | 2006-12-14
07:59:11.463733 4 | 50801.00 |
50880.00 | 2006-12-02 16:03:55.666321 | 2006-12-03
06:53:21.433746 9 | 85360.00 |
85493.00 | 2007-06-10 07:17:12.330974 | 2007-06-10
22:11:04.422656 9 | 78009.00 |
78042.00 | 2007-03-12 17:53:18.794001 | 2007-03-12
20:42:39.439647 9 | 84529.00 |
84679.00 | 2007-06-01 06:42:09.306306 | 2007-06-01
20:35:54.317172 9 | 78058.00 |
78149.00 | 2007-03-13 10:08:48.696709 | 2007-03-13
21:50:31.136412 9 | 86506.00 |
86595.00 | 2007-06-21 09:28:40.504082 | 2007-06-21
23:15:41.862292 9 | 78155.00 |
78239.00 | 2007-03-14 09:32:58.512817 | 2007-03-14
20:58:24.36362 9 | 84894.00 |
85012.00 | 2007-06-04 07:59:00.896969 | 2007-06-04
18:42:13.791974 9 | 78435.00 |
78494.00 | 2007-03-16 07:48:23.626402 | 2007-03-16
21:39:09.479043 9 | 83992.00 |
84045.00 | 2007-05-25 07:25:20.462928 | 2007-05-25
21:23:43.697577 9 | 78506.00 |
78595.00 | 2007-03-17 08:01:06.003564 | 2007-03-17
19:48:32.383689 9 | 85493.00 |
85640.00 | 2007-06-11 06:58:03.052538 | 2007-06-11
22:56:13.134053 9 | 78279.00 |
78395.00 | 2007-03-15 08:00:58.198265 | 2007-03-15
21:39:00.052173
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
can anybody help me this issue.
Thanks in advance.
Trilok
__________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.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
Dear Shane, Thanks for the reply and your observation about the word i have used. It is idle odometer reading. The actual Scenario is that the vehicle is taken by the driver. When he comes the next day. He is suppose to login again. Here i am trying to find out how much distance has the vehicle travelled before it login again,give a date range and the vehicle id. The below query would give me the total odometer reading during a single trip. select vehicle_master_id,(ending_odometer - starting_odometer) asunmetered_travelfrom vehicle_duty_cycle_summary; Thanks Trilok --- Shane Ambler <pgsql@Sheeky.Biz> wrote: > 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 > ___________________________________________________________ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/