Re: Query help - Mailing list pgsql-general

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@aklaver.com