On 1/17/19 2:44 PM, Rich Shepard wrote:
On Thu, 17 Jan 2019, Adrian Klaver wrote:
Got to thinking more and realized the answer depends on what you want the
query to produce. Can you let us know what is you are trying to pull out
with the query?
Adrian, et al.,
Took your advice and re-thought what I need the query to return. This
allowed me to realize that I don't need a separate contact history query as
I can incorporate it in a single query. The goal and pseudocode are:
Purpose: List all contact information and contact history for active people
where next contact date is less than today.
For each person select person_id, lname, fname, and direct_phone from
People.
For each person get the org_name from Organizations.
For each person get contact history in date order and next contact date
from
Contacts where active = True.
Order by next contact dates in ascending order.
Query code:
SELECT p.person_id, p.lname, p.fname, p.direct_phone, o.org_name,
c.cont_date,
c.cont_type, c.notes, c.next_contact, c.comment
FROM People AS p
JOIN Organizations AS o ON p.org_id = o.org_id
JOIN Contacts AS c ON c.person_id = p.person_id
WHERE c.active = TRUE AND c.next_contact <= 'today'::date
GROUP BY o.org_name, p.person_id, c.person_id, c.cont_date, c.cont_type,
c.next_contact
ORDER BY p.person_id, c.next_contact
The results are not correctly grouped or ordered; I'll work on fixing these
issues.
The other issue that needs fixing is identifying the most recent 'active'
value in the Contacts table for each person_id and including that person in
the results only when active = 't'. Here's a MWE of a redacted set of rows
that should not have been returned by the query:
person_id | cont_date | cont_type |
notes
| active | next_contact | comment
--------------------------+--------+--------------+---------
36 | 2018-12-12 | Conference | Notes on this conversation.
| t | 2018-12-17 |
36 | 2018-12-17 | Phone | Left message asking for a meeting.
| t | 2019-01-03 |
36 | 2019-01-03 | Phone | Left another message.
| t | 2019-01-07 |
36 | 2019-01-07 | Phone | Going into a meeting.
| t | 2019-01-14 |
36 | 2019-01-15 | Phone | Will call when/if.
| f | infinity | (5 rows)
Because the most recent row's active value is 'f' these rows should not be
included as there is no next contact date. I'll keep reading looking for
this answer and pointers will be helpful.
I would think the active would be on People or Organizations. Then you
can eliminate then from the query results before you ever got to the
contact history.
Also not sure how you the above result when the query showed:
WHERE c.active = TRUE AND c.next_contact <= 'today'::date
Or did the history results come from an undisclosed query?
Regards,
Rich
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
