Re: [SQL] sql query question ?
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) as unmetered_travel from vehicle_duty_cycle_summary; Thanks Trilok --- Shane Ambler <[EMAIL PROTECTED]> 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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Limit # of recs on inner join?
I want to limit the number of records returned by an inner join. Suppose I have a table of Books: book_id title And, a table of authors: book_id author_name Now, suppose I want to get book + author, but I only want one author for books with multiple authors. Traditionally, I'd do something like: select books.book_id, books.title, authors.author_name from books inner join authors on authors.book_id = books.book_id where books.book_id = ? This might return: 1 A Cat In The HatDr. Seuss 1 A Cat In The HatDr. Seuss' Partner Instead, I just want: 1 A Cat In The HatDr. Seuss How can I limit the inner join? Cheers, -J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Limit # of recs on inner join?
On Dec 31, 2007 1:52 PM, Josh <[EMAIL PROTECTED]> wrote: > Instead, I just want: > > 1 A Cat In The HatDr. Seuss SELECT books.book_id, books.title, (SELECT author_name FROM authors WHERE book_id = books.book_id LIMIT 1) AS author_name FROM books WHERE books.book_id = ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Limit # of recs on inner join?
SELECT DISTINCT ON ( B.book_id, B.title ) B.book_id, B.title, A.Author FROM Books AS B INNER JOIN Authors AS A ON A.book_id = B.book_id; --- On Mon, 12/31/07, Josh <[EMAIL PROTECTED]> wrote: > From: Josh <[EMAIL PROTECTED]> > Subject: [SQL] Limit # of recs on inner join? > To: pgsql-sql@postgresql.org > Date: Monday, December 31, 2007, 10:52 AM > I want to limit the number of records returned by an inner > join. > > Suppose I have a table of Books: > > book_id > title > > And, a table of authors: > > book_id > author_name > > Now, suppose I want to get book + author, but I only want > one author for > books with multiple authors. Traditionally, I'd do > something like: > > select books.book_id, books.title, authors.author_name > from books > inner join authors on authors.book_id = books.book_id > where books.book_id = ? > > This might return: > > 1 A Cat In The HatDr. Seuss > 1 A Cat In The HatDr. Seuss' Partner > > Instead, I just want: > > 1 A Cat In The HatDr. Seuss > > How can I limit the inner join? > > Cheers, > -J > > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map > settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Using rules to implement backward-compatible schema changes
Hi postgres experts, I have encountered what is, to me, a slightly tricky problem when using rules and I am in need of some help. I am using rules to simulate an old version of a database schema for backward-compatibility while migrating to a modified schema. For instance, there used to be a table a which is now replaced by table b that contains much the same data. To enable old code to still work, I create a view called a that simulates the appearance of the old table, and do instead rules to redirect all operations on it to the actual table, b. The problem is that when inserting to the view, default values do not work as expected. For instance, with a table definition and a rule like the following: CREATE TABLE b ( foo INTEGER, bar INTEGER DEFAULT 5 ); CREATE RULE insert AS ON INSERT TO a DO INSTEAD INSERT INTO b (foo,bar) VALUES (NEW.foo,NEW.bar); executing the following query: INSERT INTO a(foo) VALUES (1); results in bar being NULL instead of 5. A partial solution is to write the rule as follows: CREATE RULE insert AS ON INSERT TO a DO INSTEAD INSERT INTO b (foo,bar) VALUES (NEW.foo,COALESCE(NEW.bar,5)); effectively implementing the default value in the rule. However, this fails in the case that you explicitly insert NULL. For example: INSERT INTO a(bar) VALUES (NULL); results in bar having the value 5 instead of being NULL. Interestingly, update rules treat columns that appear in the rule but are omitted from a query as expected, i.e. such columns are not affected by the query. Is there a way of getting the behaviour that I want, or is this not possible using rules? Am I perhaps approaching this in completely the wrong way? Would there be a better way of implementing backward-compatibility with the old table definition? On a related note, I have noticed that you cannot create triggers on updateable views. Is there a reason why this could never be possible or is it not something you should want to do (e.g. because it is not possible to define clearly what this would mean) or is this essentially an arbitrary limitation that might change in the future? I am using version 8.1. Thanks for any help. -- Mario Becroft (postgres-related mail) <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Using rules to implement backward-compatible schema changes
Mario Becroft <[EMAIL PROTECTED]> writes: > The problem is that when inserting to the view, default values do not > work as expected. You need to attach the defaults to the view, viz ALTER TABLE a ALTER COLUMN bar SET DEFAULT 5; This is considered a feature not a bug, since you might want a different default for inserts to the view than for inserts directly to the underlying table. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Using rules to implement backward-compatible schema changes
Tom Lane <[EMAIL PROTECTED]> writes: > Mario Becroft <[EMAIL PROTECTED]> writes: >> The problem is that when inserting to the view, default values do not >> work as expected. > > You need to attach the defaults to the view, viz > > ALTER TABLE a ALTER COLUMN bar SET DEFAULT 5; > > This is considered a feature not a bug, since you might want a > different default for inserts to the view than for inserts > directly to the underlying table. This crossed my mind, but I could not find a way of specifying defaults for a view. I see now that it must be done with ALTER TABLE. Thanks for your help with this problem. -- Mario Becroft (postgres-related mail) <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
