Re: Query help

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(dot)klaver(at)aklaver(dot)com