Re: Query help

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.