From: | <Amit_Wadhwa(at)Dell(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Time differences between rows, not columns? |
Date: | 2005-08-30 09:44:31 |
Message-ID: | 4538E1203665624F97A8494BCF2716E5C07DFF@blrx2kmbgl102.blr.amer.dell.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Anthony Molinaro | 2005-08-30 11:28:53 | Re: Time differences between rows, not columns? |
Previous Message | Ricky Sutanto | 2005-08-30 08:42:06 | Performance issue |