On Tue, 15 Jan 2019, Thomas Kellerer wrote:
select distinct on (C.contact_id) C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact
from Contacts as C
join Organizations as O on C.org_id = O.org_id
join Activities as A on C.contact_id = A.contact_id
where A.next_contact <= 'today'
and A.next_contact > '2018-12-31'
order by c.contact_id, a.next_contact DESC;
Combining this with Adrian's advice to use BETWEEN I have this statement
that almost works:
SELECT DISTINCT ON (c.contact_id) c.contact_id, c.lname, c.fname, c.direct_phone, o.org_name, a.next_contact
FROM Contacts AS c
JOIN Organizations AS o ON c.org_id = o.org_id
JOIN Activities AS a ON c.contact_id = a.contact_id
WHERE next_contact BETWEEN '01/01/2019'::date AND 'today'::date
ORDER BY c.contact_id, a.next_contact DESC;
It fails when the most recent next_contact column in Activities is NULL and
an earier row has a non-NULL date in the specified range.
I tried specifying max(a.next_contact) and added GROUP BY, but the result
set all returned o.org_name columns to the same one.
The WHERE clause needs to exclude a contact_id where the most current row in
Activities has NULL for the next_contact column. I've tried a few ideas but
none work so I need to learn the proper syntax, and I don't find that in
Rick van der Lans' or Joe Celko's books I have.
Looking forward to learning,
Rich
