On Sat, Jan 26, 2019 at 6:30 PM Ron <ronljohnso...@gmail.com> 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 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? >
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: serv...@martinandjones.us Personal email: clmar...@ssappeals.com 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
