Thread: Query help
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:
--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
On 1/26/19 5:04 PM, Chuck Martin wrote:
Does statuschange.insdatetime 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.
I'm having trouble formulating a query. This is a simplified version of the tables:ombcase------------case_pkey integer, primary keycasename varcharinsdatetime timestamp w/o time zonestatus_fkey integer, foreign keystatus--------status_pkey integer, primary keystatusid varcharstatuschange--------statuschange_pkey integer, primary keyinsdatetime timestamp w/o time zoneombcase_fkey integer, foreign keyoldstatus_fkey integer, foreign keynewstatus_fkey integer, foreign keyactive integer, not nullableThe 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.insdatetime 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.
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 keycasename varcharinsdatetime timestamp w/o time zonestatus_fkey integer, foreign keystatus--------status_pkey integer, primary keystatusid varcharstatuschange--------statuschange_pkey integer, primary keyinsdatetime timestamp w/o time zoneombcase_fkey integer, foreign keyoldstatus_fkey integer, foreign keynewstatus_fkey integer, foreign keyactive integer, not nullableThe 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.insdatetime 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/>
Attachment
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])"
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/>
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 recordSELECTcase_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetimeFROMombcaseLEFT JOINstatuschangeONstatuschange.case_fkey = case_pkeyAND case_pkey <> 0LEFT JOINstatusON status_fkey = status_pkeyWHERE lower(statusid) NOT LIKE ('closed%')AND statuschange.statuschange_pkey IS NULLUNION--Now find the last status change record for each case that has oneSELECT DISTINCT ON (case_fkey)case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetimeFROMstatuschange,ombcase,statusWHERE case_fkey = case_pkeyAND status_fkey = status_pkeyAND LOWER(statusid) NOT LIKE ('closed%')ORDER BY case_fkey, statuschange.insdatetime DESCIf 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