From: | Chuck Martin <clmartin(at)theombudsman(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query help |
Date: | 2019-01-27 21:50:46 |
Message-ID: | CAFw6=U3PzZ8NpZ0N-Btn0jGaAr0x82kPEna0MOdVsCqkNT7stw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Chuck Martin
Avondale Software
On Sun, Jan 27, 2019 at 2:55 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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%')
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-01-27 21:52:11 | Re: Error message restarting a database |
Previous Message | Begin Daniel | 2019-01-27 21:38:22 | RE: Error message restarting a database |