On Sat, Jan 26, 2019 at 6:30 PM Ron <ronljohnsonjr@xxxxxxxxx> wrote:
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 keycasename varcharinsdatetime timestamp w/o time zonestatus_fkey integer, foreign key
status--------status_pkey integer, primary keystatusid varchar
statuschange--------statuschange_pkey integer, primary keyinsdatetime timestamp w/o time zoneombcase_fkey integer, foreign keyoldstatus_fkey integer, foreign keynewstatus_fkey integer, foreign keyactive 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?
No, it only creates a statuschange record when the status is first changed, not when the ombcase record is created.
And why not add upddatetime to ombcase? That would solve all your problems.
I do record the time of the last update, but that could reflect a change of any column (most I didn’t list).
--
Angular momentum makes the world go 'round.
Charles L. Martin
Martin Jones & Piemonte
BUSINESS email: service@xxxxxxxxxxxxxxxxx
Personal email: clmartin@xxxxxxxxxxxxx
Decatur Office:
123 N. McDonough St.
Decatur, GA 30030
404-373-3116
Fax 404-373-4110
Charlotte Office:
4601 Charlotte Park Drive, Suite 390
Charlotte, NC 28217
704-399-8890
Fax 888-490-1315
