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.*ins*datetime 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.
