Re: Refining query statement

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.

?
...

WHERE
next_contact
BETWEEN
'01/01/2019'::date AND 'today'::date
AND
a.next_contact IS NOT NULL
ORDER BY
c.contact_id, a.next_contact DESC;

>
> Looking forward to learning,
>
> Rich
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com