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@xxxxxxxxxxx
<mailto: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%')
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@xxxxxxxxxxx
