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