On 1/26/19 3: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.
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:
Outline form:
1) If a record is in ombcase it has a status('in a status') by definition.
From query below you are not looking for just records in ombcase, but
those that have a statusid other then 'closed%' in status table.
2) For the criteria in 1) you want to find the age of the last statuschange.
To me that leads to something like:
SELECT
case_pkey
FROM
ombcase AS
JOIN
status
ON
ombcase.case_pkey = status.status_fkey
LEFT JOIN
statuschange
ON -- Or statuschange.ombcase_fkey. Not clear from above.
statuschange.case_fkey = ombcase.status_pkey
GROUP BY
ombcase.pkey
HAVING
status.LOWER(statusid) NOT LIKE ('closed%')
AND
max(coalesce(statuschange.insdatetime, ombcase.insdatetime))
< 'some date'
Obviously not tested.
--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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
