On 2019-01-26 18:04:23 -0500, 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. The easiest way is to use set operations: select case_pkey from ombcase; gives you all the ombcase ids. select ombcase_fkey from statuschange where insdatetime >= now()::date - xx; gives you all ombcase ids which had a status change in the last xx days. Therefore, select case_pkey from ombcase except select ombcase_fkey from statuschange where insdatetime >= now()::date - xx; gives you all ombcase ids which did /not/ have a status change in the last xx days. Another way would be to use a CTE (https://www.postgresql.org/docs/10/queries-with.html) to extract the last status change for each ombcase and then do a left join of ombcase to that CTE. hp -- _ | Peter J. Holzer | we build much bigger, better disasters now |_|_) | | because we have much more sophisticated | | | hjp@xxxxxx | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Attachment:
signature.asc
Description: PGP signature
