On 2/13/19 6:28 AM, Rich Shepard wrote:
> On Tue, 12 Feb 2019, Ken Tanzer wrote:
>
>> 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)
>> ;
>
> I modified this to restrict the time and ignore nulls by replacing the
> question marks:
>
> WHERE a.next_contact <= 'today' and a.next_contact > '2018-12-31' and
> a.next_contact is not null
>
> For a reason I've not yet found, the last condition is not observed; i.e.,
> those rows with null next_contact dates appear in the results. Position in
> the sequence makes no difference. What might cause this?
The LEFT JOIN. There are rows in people for which there no records
coming from the sub-select on activities, so the row is 'padded' with
NULL values for the missing data.
>
> Regards,
>
> Rich
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com