From: | Charles Martin <ssappeals(at)gmail(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Query help |
Date: | 2019-01-27 02:38:23 |
Message-ID: | CAFw6=U1U8CqeZ0RfyZXSACo0mbubj9SvO9dRV5UUUd9AOKFXLA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Jan 26, 2019 at 6:30 PM Ron <ronljohnsonjr(at)gmail(dot)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: service(at)martinandjones(dot)us
Personal email: clmartin(at)ssappeals(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2019-01-27 13:07:16 | Re: Query help |
Previous Message | Ron | 2019-01-26 23:30:15 | Re: Query help |