On Tue, 15 Jan 2019, Rich Shepard wrote:
Working with my sales/client management system using psql I have a select
statement to identify contacts to be made. This statement works:
With much patient advice from Adrian, David, Thomas, and Ron I fixed the
schema and the query statement. To close
On Thu, 17 Jan 2019, David G. Johnston wrote:
I would advise changing Contacts to "Activities" as the former can readily
be interpreted (and is in the wild) as both "an instance of contacting a
person" and "the person at the organization who is being contacted" (i.e.,
your People class).
David
On Thu, Jan 17, 2019 at 3:44 PM Rich Shepard wrote:
> FROM People AS p
> JOIN Organizations AS o ON p.org_id = o.org_id
> JOIN Contacts AS c ON c.person_id = p.person_id
I would advise changing Contacts to "Activities" as the former can
readily be interpreted (and is in the wild) as b
On Thu, 17 Jan 2019, Adrian Klaver wrote:
I would think the active would be on People or Organizations. Then you can
eliminate then from the query results before you ever got to the contact
history.
Adrian,
Excellent point. I don't know why I put active in the contacts table as it
does make m
On 1/17/19 2:44 PM, Rich Shepard wrote:
On Thu, 17 Jan 2019, Adrian Klaver wrote:
Got to thinking more and realized the answer depends on what you want the
query to produce. Can you let us know what is you are trying to pull out
with the query?
Adrian, et al.,
Took your advice and re-thought
On Thu, 17 Jan 2019, Adrian Klaver wrote:
Got to thinking more and realized the answer depends on what you want the
query to produce. Can you let us know what is you are trying to pull out
with the query?
Adrian, et al.,
Took your advice and re-thought what I need the query to return. This
al
On Thu, 17 Jan 2019, Adrian Klaver wrote:
It would produce results, so yes it would work. The question are they the
results you want?
Adrian,
To which the answer is no as I just discovered.
The above would return anything with a next_contact less then today. That
could extend backwards to
On 1/17/19 10:01 AM, Rich Shepard wrote:
On Thu, 17 Jan 2019, Adrian Klaver wrote:
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':
WHERE COALESCE
On Thu, 17 Jan 2019, David G. Johnston wrote:
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:
David,
Adrian's suggestion of a Contacts table column called 'active' having a
boolean data type
On Thu, 17 Jan 2019, Adrian Klaver wrote:
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':
WHERE COALESCE(next_contact, 'infinity') BETWEEN '01/01/
On Thu, 17 Jan 2019, Adrian Klaver wrote:
To be clear the next-activity date = next_contact in the database, correct?
Adrian,
Yes. I've renamed the Activities table to Contacts and the Contacts table
to People.
NULL basically means unknown, so having it stand for something is a bit of
a s
On Thu, Jan 17, 2019 at 10:07 AM Rich Shepard 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
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
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.
On Thu, Jan 17, 2019 at 9:47 AM Rich Shepard wrote:
> What I want is a list of contacts to make today. This includes ones that
> should have been made earlier but weren't and excludes earlier contacts that
> have no scheduled next contact (therefore, the nulls.).
>
> Does this clarify what I'm ask
On Thu, 17 Jan 2019, Adrian Klaver wrote:
Got to thinking more and realized the answer depends on what you want the
query to produce. Can you let us know what is you are trying to pull out
with the query?
Adrian,
Certainly. Over breakfast I realized the same thing: the existing SELECT
query i
On Thu, 17 Jan 2019, David G. Johnston wrote:
Why is next_contact allowed to be null?
David,
There are a number of reasons. The prospect might have retired, told me to
pound sand, or has put off a decision.
Your concept of "most current row" is strictly based upon next_contact so
if next_co
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
On Thu, Jan 17, 2019 at 9:14 AM Rich Shepard wrote:
> ORDER BY c.contact_id, a.next_contact DESC;
> The WHERE clause needs to exclude a contact_id where the most current row in
> Activities has NULL for the next_contact column.
Why is next_contact allowed to be null?
Your concept of "most curre
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
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
On 1/15/19 9:02 AM, Ron 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
Adrian Klaver schrieb am 15.01.2019 um 17:44:
So we end up with something like this:
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 Activi
On Tue, 15 Jan 2019, Adrian Klaver wrote:
Use BETWEEN?:
https://www.postgresql.org/docs/10/functions-comparison.html
a BETWEEN x AND y between
So:
next_contact BETWEEN '01/01/2019'::date AND '01/15/2019'::date
Or a range function:
https://www.postgresql.org/docs/10/functions-range.html
On 1/15/19 8:02 AM, Ron wrote:
the best way to do it:
So we end up with something like this:
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
On 1/15/19 8:26 AM, Rich Shepard wrote:
On Tue, 15 Jan 2019, Adrian Klaver wrote:
For the above I could see using a datepicker widget that allows for
multidate select. The specifics would depend on the software you are
using
to write the UI.
Adrian,
I'm using wxPython4, and I will use a
On Tue, 15 Jan 2019, Ron wrote:
And I've never liked this method (though I'm old and crotchety)
Ron,
I'm older but not crotchety (most days), and I'm taking my SQL knowledge
beyone what I've used in the past. I would appreciate you're explaining why
you don't like the explicit JOINs pre
On Tue, 15 Jan 2019, Thomas Kellerer wrote:
With regards to "cleaner": the first thing to do is to remove the
parentheses around the column list. In Postgres "(a,b,c)" creates a single
column with an anonymous record type (that contains three fields), rather
than selecting three columns. In othe
On Tue, 15 Jan 2019, Adrian Klaver wrote:
For the above I could see using a datepicker widget that allows for
multidate select. The specifics would depend on the software you are using
to write the UI.
Adrian,
I'm using wxPython4, and I will use a calendar with that. But, is there a
way to
On 1/15/19 9:47 AM, Thomas Kellerer wrote:
Rich Shepard schrieb am 15.01.2019 um 16:39:
Working with my sales/client management system using psql I have a select
statement to identify contacts to be made. This statement works:
select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_nam
On 1/15/19 7:39 AM, Rich Shepard wrote:
Working with my sales/client management system using psql I have a
select
statement to identify contacts to be made. This statement works:
select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name,
A.next_contact)
from Contacts as C, Organiz
Rich Shepard schrieb am 15.01.2019 um 16:39:
> Working with my sales/client management system using psql I have a select
> statement to identify contacts to be made. This statement works:
>
> select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name,
> A.next_contact)
> from Contacts a
Working with my sales/client management system using psql I have a select
statement to identify contacts to be made. This statement works:
select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name,
A.next_contact)
from Contacts as C, Organizations as O, Activities as A
where C.org_id
33 matches
Mail list logo
