Re: Query help - Mailing list pgsql-general

On 1/27/19 1:50 PM, Chuck Martin wrote:
> 
> Chuck Martin
> Avondale Software
> 
> 
> On Sun, Jan 27, 2019 at 2:55 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 1/26/19 3:04 PM, Chuck Martin wrote:
>     [snip]
>     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.
> 
> 
> Thanks, Adrian. This got me a lot closer, but I'm puzzled by the number 
> of records returned. There are 3120 ombcase records with a statusid that 
> is <> 'closed%'

> 
> SELECT count(ombcase.case_pkey)
> 
> FROM ombcase,status
> 
> WHERE ombcase.status_fkey = status.status_pkey  AND 
> lower(status.statusid) NOT LIKE  ('closed%')

To get an apples to apples comparison what does below return?:

SELECT  count(ombcase.case_pkey)
FROM ombcase

    INNER JOIN status

    ON ombcase.status_fkey = status.status_pkey

        LEFT JOIN statuschange

        ON statuschange.case_fkey = ombcase.case_pkey
AND
    LOWER(status.statusid) NOT LIKE ('closed%')


Best guess is the 258 records are the ombcase records that have no 
statuschange records, brought in by the LEFT JOIN.

> 
> 
> But 3378 are returned by:
> 
> SELECT  ombcase.case_pkey, ombcase.casename, 
> COALESCE(AGE(statuschange.insdatetime), AGE(ombcase.insdatetime)) AS 
> age_in_status
> 
> FROM ombcase
> 
> INNER JOIN status
> 
> ON ombcase.status_fkey = status.status_pkey
> 
> LEFT JOIN statuschange
> 
> ON statuschange.case_fkey = ombcase.case_pkey
> 
> GROUP BY ombcase.case_pkey, status.statusid, statuschange.insdatetime, 
> ombcase.insdatetime
> 
> HAVING LOWER(status.statusid) NOT LIKE ('closed%')
> 
> AND ombcase.case_pkey <> 0
> 
> AND MAX(COALESCE(AGE(statuschange.insdatetime), 
> AGE(ombcase.insdatetime))) > '2 months'
> 
> ORDER BY age_in_status DESC
> 
> I don't know where the extra 258 records came from, and I think I need 
> to keep working on it until the query returns 3120 records.


-- 
Adrian Klaver
adrian.klaver@aklaver.com