Query help

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.

This query returns the age of each ombcase and the last statuschange
record, but only if there is a statuschange record:

--Finds the age and last status change for open cases, but not age of cases
with no status change

SELECT casename, age(ombcase.insdatetime) AS caseage,
age(laststatuschange.created_at) AS statusage

FROM

(SELECT

case_fkey, MAX(insdatetime) AS created_at

FROM

statuschange

GROUP BY

case_fkey) AS laststatuschange

INNER JOIN

ombcase

ON

laststatuschange.case_fkey = case_pkey

RIGHT JOIN status

ON status_fkey = status_pkey

WHERE lower(statusid) NOT LIKE ('closed%')

AND case_pkey <> 0

I want to use coalesce
(age(ombcase.insdatetime),age(statuschange.insdatetime) ) to return the
time that a case has been in a status—or without a status change. But first
I have to find the cases with no statuschange record. I was able to do
that, too, using this query:

--find cases in status too long

SELECT casename, coalesce
(age(ombcase.insdatetime),age(statuschange.insdatetime) )

FROM ombcase

LEFT JOIN statuschange

ON case_fkey = case_pkey

LEFT JOIN status

ON status_fkey = status_pkey

AND lower(statusid) NOT LIKE ('closed%')

AND coalesce ( age(ombcase.insdatetime), age(statuschange.insdatetime) ) >
'2 months'

But this query will return all statuschange records for an ombcase record
that has multiple ones.

Any suggestions on how to combine the two ideas?

Chuck Martin
Avondale Software
--
Chuck Martin
Avondale Software