Query help

[Chuck Martin <clmartin@xxxxxxxxxxxxxxxx>]

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