Query help

On 1/26/19 5:04 PM, Chuck Martin wrote:

I'm having trouble formulating a query. This is a simplified version of
the tables:

ombcase
------------
case_pkey integer, primary key
casename varchar
insdatetime timestamp w/o time zone
status_fkey integer, foreign key

status
--------
status_pkey integer, primary key
statusid varchar

statuschange
--------
statuschange_pkey integer, primary key
insdatetime timestamp w/o time zone
ombcase_fkey integer, foreign key
oldstatus_fkey integer, foreign key
newstatus_fkey integer, foreign key
active integer, not nullable

The idea should be obvious, but to explain, insdatetime is set when a new
record is created in any table. All records in ombcase have a foreign key
to status that can't be null. When status changes, a record is created in
statuschange recording the old and new status keys, and the time (etc).

The goal is to find records in ombcase that have not had a status change
in xx days. If the status has not changed, there will be no statuschange
record.

Does statuschange.*ins*datetime record when an ombcase record was first
inserted, or when the status_fkey associated with ombcase.case_pkey was updated?

And why not add upddatetime to ombcase?  That would solve all your problems.

--
Angular momentum makes the world go 'round.

On Sat, Jan 26, 2019 at 6:30 PM Ron <ronljohnsonjr@gmail.com> wrote:

On 1/26/19 5:04 PM, Chuck Martin wrote:

I'm having trouble formulating a query. This is a simplified version of
the tables:

ombcase
------------
case_pkey integer, primary key
casename varchar
insdatetime timestamp w/o time zone
status_fkey integer, foreign key

status
--------
status_pkey integer, primary key
statusid varchar

statuschange
--------
statuschange_pkey integer, primary key
insdatetime timestamp w/o time zone
ombcase_fkey integer, foreign key
oldstatus_fkey integer, foreign key
newstatus_fkey integer, foreign key
active integer, not nullable

The idea should be obvious, but to explain, insdatetime is set when a new
record is created in any table. All records in ombcase have a foreign key
to status that can't be null. When status changes, a record is created in
statuschange recording the old and new status keys, and the time (etc).

The goal is to find records in ombcase that have not had a status change
in xx days. If the status has not changed, there will be no statuschange
record.

Does statuschange.*ins*datetime record when an ombcase record was first
inserted, or when the status_fkey associated with ombcase.case_pkey was
updated?

No, it only creates a statuschange record when the status is first changed,
not when the ombcase record is created.

And why not add upddatetime to ombcase? That would solve all your
problems.

I do record the time of the last update, but that could reflect a change of
any column (most I didn’t list).

--
Angular momentum makes the world go 'round.

--

Charles L. Martin
Martin Jones & Piemonte
BUSINESS email: service@martinandjones.us
Personal email: clmartin@ssappeals.com
Decatur Office:
123 N. McDonough St.
Decatur, GA 30030
404-373-3116
Fax 404-373-4110

Charlotte Office:
4601 Charlotte Park Drive, Suite 390
Charlotte, NC 28217
704-399-8890
Fax 888-490-1315

On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:

I'm having trouble formulating a query. This is a simplified version of the
tables:

ombcase
------------
case_pkey integer, primary key
casename varchar
insdatetime timestamp w/o time zone
status_fkey integer, foreign key

status
--------
status_pkey integer, primary key
statusid varchar

statuschange
--------
statuschange_pkey integer, primary key
insdatetime timestamp w/o time zone
ombcase_fkey integer, foreign key
oldstatus_fkey integer, foreign key
newstatus_fkey integer, foreign key
active integer, not nullable

The idea should be obvious, but to explain, insdatetime is set when a new
record is created in any table. All records in ombcase have a foreign key to
status that can't be null. When status changes, a record is created in
statuschange recording the old and new status keys, and the time (etc). 

The goal is to find records in ombcase that have not had a status change in xx
days. If the status has not changed, there will be no statuschange record. 

The easiest way is to use set operations:

select case_pkey from ombcase;
gives you all the ombcase ids.

select ombcase_fkey from statuschange where insdatetime >= now()::date - xx;
gives you all ombcase ids which had a status change in the last xx days.

Therefore,
select case_pkey from ombcase
except
select ombcase_fkey from statuschange where insdatetime >= now()::date - xx;
gives you all ombcase ids which did /not/ have a status change in the
last xx days.

Another way would be to use a CTE
(https://www.postgresql.org/docs/10/queries-with.html) to extract the
last status change for each ombcase and then do a left join of ombcase
to that CTE.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

On 1/26/19 3:04 PM, Chuck Martin wrote:

I'm having trouble formulating a query. This is a simplified version of
the tables:

ombcase
------------
case_pkey integer, primary key
casename varchar
insdatetime timestamp w/o time zone
status_fkey integer, foreign key

status
--------
status_pkey integer, primary key
statusid varchar

statuschange
--------
statuschange_pkey integer, primary key
insdatetime timestamp w/o time zone
ombcase_fkey integer, foreign key
oldstatus_fkey integer, foreign key
newstatus_fkey integer, foreign key
active integer, not nullable

The idea should be obvious, but to explain, insdatetime is set when a
new record is created in any table. All records in ombcase have a
foreign key to status that can't be null. When status changes, a record
is created in statuschange recording the old and new status keys, and
the time (etc).

The goal is to find records in ombcase that have not had a status change
in xx days. If the status has not changed, there will be no statuschange
record.

This query returns the age of each ombcase and the last statuschange
record, but only if there is a statuschange record:

--Finds the age and last status change for open cases, but not age of
cases with no status change

SELECT casename, age(ombcase.insdatetime) AS caseage,
age(laststatuschange.created_at) AS statusage

FROM

(SELECT

case_fkey, MAX(insdatetime) AS created_at

FROM

statuschange

GROUP BY

case_fkey) AS laststatuschange

INNER JOIN

ombcase

ON

laststatuschange.case_fkey = case_pkey

RIGHT JOIN status

ON status_fkey = status_pkey

WHERE lower(statusid) NOT LIKE ('closed%')

AND case_pkey <> 0

I want to use coalesce
(age(ombcase.insdatetime),age(statuschange.insdatetime) ) to return the
time that a case has been in a status—or without a status change. But
first I have to find the cases with no statuschange record. I was able
to do that, too, using this query:

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.

--find cases in status too long

SELECT  casename, coalesce
(age(ombcase.insdatetime),age(statuschange.insdatetime) )

FROM ombcase

LEFT JOIN statuschange

ON case_fkey = case_pkey

LEFT JOIN status

ON status_fkey = status_pkey

AND lower(statusid) NOT LIKE ('closed%')

AND coalesce ( age(ombcase.insdatetime), age(statuschange.insdatetime) )

'2 months'

But this query will return all statuschange records for an ombcase
record that has multiple ones.

Any suggestions on how to combine the two ideas?

Chuck Martin
Avondale Software
--
Chuck Martin
Avondale Software

--
Adrian Klaver
adrian.klaver@aklaver.com

Chuck Martin
Avondale Software

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

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.

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

On Sun, Jan 27, 2019 at 8:07 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:
[snip]

The idea should be obvious, but to explain, insdatetime is set when a new
record is created in any table. All records in ombcase have a foreign

key to

status that can't be null. When status changes, a record is created in
statuschange recording the old and new status keys, and the time (etc).

The goal is to find records in ombcase that have not had a status change

in xx

days. If the status has not changed, there will be no statuschange

record.

The easiest way is to use set operations:

select case_pkey from ombcase;
gives you all the ombcase ids.

select ombcase_fkey from statuschange where insdatetime >= now()::date -
xx;
gives you all ombcase ids which had a status change in the last xx days.

Therefore,
select case_pkey from ombcase
except
select ombcase_fkey from statuschange where insdatetime >= now()::date -
xx;
gives you all ombcase ids which did /not/ have a status change in the
last xx days.

I was not familiar with set operations, but studied up a bit and thought I
was getting there. Not quite, though. I have two queries that individually
return 1) all ombcase records with no statuschange record, and 2) the
newest statuschange record for each case that has a statuschange record.
But just putting UNION between then doesn't work. Here are my queries:

--First, find all open cases with no statuschange record
SELECT

case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
ombcase
LEFT JOIN
statuschange
ON
statuschange.case_fkey = case_pkey
AND case_pkey <> 0
LEFT JOIN
status
ON status_fkey = status_pkey
WHERE lower(statusid) NOT LIKE ('closed%')
AND statuschange.statuschange_pkey IS NULL
UNION
--Now find the last status change record for each case that has one
SELECT DISTINCT ON (case_fkey)

case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
statuschange,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND LOWER(statusid) NOT LIKE ('closed%')
ORDER BY case_fkey, statuschange.insdatetime DESC

If I run each part separately, I get the expected number of records.
When I combine them with UNION, I get "missing FROM-clause entry for
table "statuschange"
So I'm very close here, and these two return the exact number of
records I'm expecting. So I just need to get them added together. Then
I expect I can put the whole thing in a WHERE clause with "AND
ombcase.case_pkey IN ([the combined results])"

On Sun, Jan 27, 2019 at 5:27 PM Chuck Martin <clmartin@theombudsman.com>
wrote:

On Sun, Jan 27, 2019 at 8:07 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:
[snip]

The idea should be obvious, but to explain, insdatetime is set when a

new

record is created in any table. All records in ombcase have a foreign

key to

status that can't be null. When status changes, a record is created in
statuschange recording the old and new status keys, and the time (etc).

The goal is to find records in ombcase that have not had a status

change in xx

days. If the status has not changed, there will be no statuschange

record.

The easiest way is to use set operations:

select case_pkey from ombcase;
gives you all the ombcase ids.

select ombcase_fkey from statuschange where insdatetime >= now()::date -
xx;
gives you all ombcase ids which had a status change in the last xx days.

Therefore,
select case_pkey from ombcase
except
select ombcase_fkey from statuschange where insdatetime >= now()::date -
xx;
gives you all ombcase ids which did /not/ have a status change in the
last xx days.

I was not familiar with set operations, but studied up a bit and thought I
was getting there. Not quite, though. I have two queries that individually
return 1) all ombcase records with no statuschange record, and 2) the
newest statuschange record for each case that has a statuschange record.
But just putting UNION between then doesn't work. Here are my queries:

--First, find all open cases with no statuschange record
SELECT

case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
ombcase
LEFT JOIN
statuschange
ON
statuschange.case_fkey = case_pkey
AND case_pkey <> 0
LEFT JOIN
status
ON status_fkey = status_pkey
WHERE lower(statusid) NOT LIKE ('closed%')
AND statuschange.statuschange_pkey IS NULL
UNION
--Now find the last status change record for each case that has one
SELECT DISTINCT ON (case_fkey)

case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
statuschange,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND LOWER(statusid) NOT LIKE ('closed%')
ORDER BY case_fkey, statuschange.insdatetime DESC

If I run each part separately, I get the expected number of records. When I combine them with UNION, I get "missing FROM-clause entry for table "statuschange"
So I'm very close here, and these two return the exact number of records I'm expecting. So I just need to get them added together. Then I expect I can put the whole thing in a WHERE clause with "AND ombcase.case_pkey IN ([the combined results])"

This was pretty easy to resolve. Putting parentheses around each half of

the query caused it to return the right results. Then I could reduce the
columns to just ombcase.case_pkey and use an IN statement. I think this
gets me where I need to be. I appreciate the help!

Chuck