Search Postgresql Archives

Re: Query help

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 1/26/19 5:04 PM, Chuck Martin wrote:
I'm having trouble formulating a query. This is a simplified version of the tables:

ombcase
------------
case_pkey integer, primary key
casename varchar
insdatetime timestamp w/o time zone
status_fkey integer, foreign key

status
--------
status_pkey integer, primary key
statusid varchar

statuschange
--------
statuschange_pkey integer, primary key
insdatetime timestamp w/o time zone
ombcase_fkey integer, foreign key
oldstatus_fkey integer, foreign key
newstatus_fkey integer, foreign key
active integer, not nullable

The idea should be obvious, but to explain, insdatetime is set when a new record is created in any table. All records in ombcase have a foreign key to status that can't be null. When status changes, a record is created in statuschange recording the old and new status keys, and the time (etc). 

The goal is to find records in ombcase that have not had a status change in xx days. If the status has not changed, there will be no statuschange record.

Does statuschange.insdatetime record when an ombcase record was first inserted, or when the status_fkey associated with ombcase.case_pkey was updated?

And why not add upddatetime to ombcase?  That would solve all your problems.

--
Angular momentum makes the world go 'round.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux