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