Re: Refining query statement

On 1/17/19 9:07 AM, Rich Shepard wrote:
> On Thu, 17 Jan 2019, David G. Johnston wrote:
>
>> Yes...though now it just sounds like a flawed data model.
>
> David,
>
>   This is what I thought.
>
>> How stuck are you in that regard? Those "future" contacts should have
>> their
>> own records and not be derived via an optional field on an existing
>> record.
>
>   My goal is to make a functioning business tracking application for my
> consulting services. Almost all my prior postgres databases hold
> environmental data for statistical and spatio-temporal analyses so
> writing a
> business application is a new experience for me and I want to get it
> correct.
>
>> Put differently, how do you know which activities are completed and
>> which are not?
>
>   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'.)

To be clear the next-activity date = next_contact in the database, correct?

NULL basically means unknown, so having it stand for something is a bit
of a stretch. Seems to me a boolean field of name active to denote
contacts you need to keep up with is in order. Then make the
next_contact field NOT NULL and replace the current NULL values with
'infinity':

https://www.postgresql.org/docs/10/datatype-datetime.html

8.5.1.4. Special Values

As to current situation:

1) Use my previous suggestion.

or

2) WHERE COALESCE(next_contact, 'infinity') BETWEEN '01/01/2019'::date
AND 'today'::date

https://www.postgresql.org/docs/10/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

>
>   I should rename the Contacts table as People and the Activities table as
> Contacts. The original names came from a sales management system I used
> as a
> design guide, but they're probably confusing to others as well as to me.
> :-)
>
>   I can provide my current schema (eight tables) to the list (perhaps
> as an
> attachment), an individual, or put in on a cloud site and pass the URL.
>
> Thanks,
>
> Rich
>

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