> > > Ken, > > Yes, cheers indeed. A bit of thinking and re-organizing resulted in a > working statement that's close to what I want: > > select (P.person_id, P.lname, P.fname, P.direct_phone, O.org_name, > (select max(A.next_contact))) > from People as P, Organizations as O, Activities as A > where P.org_id = O.org_id and P.person_id = A.person_id and > /*A.next_contact = (select max(A.next_contact) from Activities as > A) and */ > A.next_contact <= 'today' and A.next_contact > '2018-12-31' and > A.next_contact is not null > group by A.next_contact, O.org_id, P.person_id; > > The two issues I now focus on resolving are the multiple rows per person > rather than only the most recent and the date displayed at the end of each > output row. DISTINCT ON will eliminate the first issue. > > If that's getting you what you want, then great and more power to you. It looks like you'll only get people who have a next_contact in your target window there. You might also consider something like this...
select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, a.next_contact from people as p LEFT JOIN organizations o USING (person_id) LEFT JOIN ( SELECT DISTINCT ON (person_id) person_id, next_contact FROM activities a -- WHERE ??? ORDER BY person_id,next_contact DESC ) a USING (person_id) ; Cheers, Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.