Search Postgresql Archives

Re: Query help

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Chuck Martin
Avondale Software


On Sun, Jan 27, 2019 at 2:55 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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%')


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. 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux