Re: Refining query statement - Mailing list pgsql-general

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.

Regards,

Rich