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
