[SQL] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] sql query question ?
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] temp table existence
Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables is not enough because there may be other such tables created in other sessions. Or maybe anyone knows the identification (apart from 'others') of error to trap it with EXCEPTION clause?
Re: [SQL] temp table existence
Hello On 29/12/2007, Marcin Krawczyk <[EMAIL PROTECTED]> wrote: > Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to > check i it exists before I create it. > Doing simple check on pg_class or pg_tables is > not enough because there may be other such tables created in other sessions. > Or maybe anyone knows the identification (apart from 'others') of error to > trap it with EXCEPTION clause? > http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL Regards Pavel Stehule ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] temp table existence
Thanks for the answer but it's not quite sufficient. The code supplied on his page: CREATE OR REPLACE FUNCTION ... BEGIN PERFORM 1 FROM pg_catalog.pg_tables WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp%'; IF FOUND THEN TRUNCATE xx; ELSE CREATE TEMP TABLE xx(... END IF; The function does exactly what I was trying to avoid - simple check the existence of xx table in pg_tables virtualy only by it's name, it's not enough since there may be other temp tables created in seprate sessions. The only thing those temp table differ in pg_tables i schemaname, they have that suffix number and in the above mentioned function I would have to be able to retrieve this number somehow. ... WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp_' || function_to_retieve_suffix() ??? ... That would work. Otherwise all temp tables by the name of xx will be truncated, which I would not like to happen since since they may still be in use. 2007/12/29, Marcin Krawczyk <[EMAIL PROTECTED]>: > > Hi all. Is there a way to determine the existence of a TEMP > TABLE? I need to check i it exists before I create it. Doing simple check on > pg_class or pg_tables is > not enough because there may be other such tables created in other > sessions. Or maybe anyone knows the identification (apart from 'others') of > error to trap it with EXCEPTION clause? >
Re: [SQL] temp table existence
I just realized something... my bad. It will work since TRUNCATE removes only table from current session. Thank you again. Regards
Re: [SQL] temp table existence
Hello
my solution isn't 100% perfect too. Better is test visibility:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog' -- replace LIKE 'pg_temp%';
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
for existence test this query can be simplified
Regards
Pavel Stehule
On 29/12/2007, Marcin Krawczyk <[EMAIL PROTECTED]> wrote:
> I just realized something... my bad. It will work since TRUNCATE removes
> only table from current session.
>
> Thank you again.
> Regards
>
>
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] temp table existence
- Original message -- From: "Marcin Krawczyk" <[EMAIL PROTECTED]> > Hi all. Is there a way to determine the existence of a TEMP > TABLE? I need to check i it exists before I create it. Doing simple > check on pg_class or pg_tables is > not enough because there may be other such tables created in other sessions. > Or maybe anyone knows the identification (apart from 'others') of error to > trap it with EXCEPTION clause? select * from temp_table limit 1; Trap error 42P01 Undefined table. -- Adrian Klaver [EMAIL PROTECTED] - --- Begin Message --- Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables isnot enough because there may be other such tables created in other sessions. Or maybe anyone knows the identification (apart from 'others') of error to trap it with EXCEPTION clause? --- End Message --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] temp table existence
On Dec 29, 2007, at 3:16 PM, Marcin Krawczyk wrote: I just realized something... my bad. It will work since TRUNCATE removes only table from current session. If the table exists and you're going to TRUNCATE it before using it, you could just use DROP TABLE IF EXISTS and then create it. I don't know, that feels cleaner to me than TRUNCATEing a table that might not be available to the session. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
