Re: Query help

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(dot)klaver(at)aklaver(dot)com
> <mailto: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%')

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(dot)klaver(at)aklaver(dot)com