Time differences between rows, not columns?
От | |
---|---|
Тема | Time differences between rows, not columns? |
Дата | |
Msg-id | 4538E1203665624F97A8494BCF2716E5C07DFF@blrx2kmbgl102.blr.amer.dell.com обсуждение исходный текст |
Список | pgsql-sql |
Using postgresSQL 8.0 for windows, running on windows 2003 server 16gb RAM, 3Ghz dual p4. Language: Java - JDBC postgres driver: postgresql-8.0-310.jdbc3.jar I have an application with 3 tables (in this context that is) Table bills bill_id NOT NULL serial ... And other columns Table bill_status bill_id (references bills.bill_id) statusid int4 (references bill_statuslookup.statusid) statustime datetime Table bill_statuslookup statusid serial not null statusname varchar(255) The application basically tracks a workflow of bills flowing fromone department to another. Everytime the bill moves one step, an entry is made into the bill_status table. Eg. Bills table ------------- Bill_id otherfield1 1 ........ Bill_status table: --------------------- Bill_id statusid statustime 1 10 2005-04-04 00:34:31 1 20 2005-04-05 00:55:00 Bill_statuslookup table: ------------------------- Statusid Statusname 10 submitted 20 received 30 rejected 40 accepted .. .. Now my problem is this: 1. Find the time taken for each bill to reach from status 10 to status 20 , given the time of status 10 should be between t1 and t2. Eg I want to know how much time it took for a bill to be accepted after it was submitted (criteria: submitted between yesterday and today) 2. I want to know how many bills took <7 days, how many tok 7-15 days, how many took >15 days etc. The status is a lookup table because the workflow constantly changes, and I can't have submitted_on, recd_on accepted_on times etc in the main bills table as columns because there are way too many statuses in the life of a bill (read invoice). Hope its clear as to what I'm looking for. Any help is greatly appreciated!! Regards, Amit
В списке pgsql-sql по дате отправления: