On 1/17/19 8:14 AM, Rich Shepard wrote:
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.
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?
Looking forward to learning,
Rich
--
Adrian Klaver
adrian.kla...@aklaver.com
