Re: Refining query statement

On Thu, Jan 17, 2019 at 10:07 AM Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
> The direct answer is that a completed activity has a row with either a
> future next-activity date or a null (which is the case when the status of
> that organization or contact is 'no further contact'.)

Off the top of my head (and this is a model I am quite familiar with
even if I'm doing this email at speed):

I'd suggest an actual activity table:

activity (
created_on date not null,
due_on date not null,
performed_on date null,
contact_id bigint not null references (contact)
);

contact (
contact_id bigserial primary key,
told_me_to_go_pound_sand boolean default false
);

Now, an exception report can be made for every contact where
"told_me_to_go_pound_sand" is false and there is no record on activity
where performed_on is null (and also pound sand is true and there is
one performed_on is null)

The report you want is basically everything on activity where
performed_on is null and due_on is today or earlier.

A unique partial index can be made (activity.contact_id) where
performed_on is null; to ensure that at most only one open activity
exists for each contact.

David J.