Re: Refining query statement - Mailing list pgsql-general

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@aklaver.com