Search Postgresql Archives

Date

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,
   I have a couple of tables.  The client tables and the contacted
tables.
I am not sure how to start on this, what I need is a way to query  all
my clients
then show any client that the last visit  and or called day is greater
than 30 days.
Now it gets confusing, Suppose the client was visited more than 30 days
ago
but was called only 10 days ago, I really would like to have this
appear on the same
query.
So the report would look similar to this below (based on info given
later).
Client ID               Visit Date         Called Date
1                               ---                       ---
2                                                2006-11-01
3                                                2006-11-01
5                                                2006-11-03
6                          2006-11-03


Fields (Simplified)
Clients:  Name, Address, Phone.
Contacted:  Name, Date, Visit, Call.
I need to query all names, but I only need the last visit and last
phone call(which could be the same record in the contacted table).
Then determine if either  date is greater than 30 days  if
so, display the last date of each type of contact. And if there is
nothing for the client in the contacted table this needs to show also,
ClientD.
Any tips, ideas would be greatly appreciated....
Thanks
Ice

CREATE TABLE contacted (
    "key" serial NOT NULL,
    "Date" date NOT NULL,
    "Phone" boolean DEFAULT false NOT NULL,
    "Visit" boolean DEFAULT false NOT NULL,
    "Reason" character varying(255),
    "Results" character varying(255),
    "Comments" character varying(255),
    id integer NOT NULL,
    "Enumber" integer NOT NULL,
    fup boolean DEFAULT true NOT NULL,
    fupdate date
);

CREATE TABLE clients (
    lname character varying(30),
    fname character varying(30),
    company character varying(40),
    address1 character varying(30),
    address2 character varying(30),
    city character varying(30),
    state character(2),
    zip character(10),
    active boolean DEFAULT true,
    id integer DEFAULT nextval('id_seq'::regclass) NOT NULL
);

COPY clients (lname, fname, company, address1, address2, city, state,
zip, active, id) FROM stdin;
Smith   Joe     Small Co        Smallville Rd           Bigton  NY
12234           t       1
Doe     Jane    Dust Grabber Inc        10 Dirt Drive           Dustin
PA      12345-1222      t       2
Smacher Frank   Woodwerkers Inc 100 Forest
Lane            Oakland CA      12346-2222      t       3
Zimbob  Roger   Drywallz Inc    1 Gympsum Place         Quarryville
NY      12347
        t       4
Deckem  Will    Porches are us  2 Backyard Lane         Gazeboton
CO      12348           t       5
Crimp   Greg    Kidocker        2 Tenfly Rd             Metropolis
NY      10002           t       6
\.

--

COPY contacted ("key", "Date", "Phone", "Visit", "Reason", "Results",
"Comments", id, "Enumber", fup, fupdate) FROM stdin;
1       2006-11-01      t       f       Promote new filters     Would
like a sample     Sounds very
interested      2       602     t       2006-11-15
2       2006-11-01      t       f       Promote new filter      Send
Sample     Sounds very
interested      3       602     t       2006-11-15
3       2006-11-02      f       t       Demo new air purifier   Glitch
in servo motor, would not
rotate the exhaust fan. Smoke coming from inside.       Demo bombed,
due to a
faulty oscilator motor.  \nThey will call us.\nDiscussed with
engineers.      4       602     f       \N
4       2006-11-03      t       f       Setup appointment       Setup
appointment for
11/28/2006      need to remind the day before   5       603     t
2008-11-27
5       2006-11-03      f       t       Demo Puro-203   Demo went
flawless.  William seemed to
be very impressed       Left several pamphlets on other models for home
and
business.       6       605     t       2006-11-17
6       2006-12-10      t       f       Just to see if they would like
to have 30 day eval of
the Puro-206d   Seemed interested they needed to talk with their
facilities manager      Need this sale after last
disaster        4       605     t       2006-12-15
7       2006-12-15      t       f       Follow up       Have
appointment to install 2 30 day evals
of the Puro-206d 12/20/2006     Need to appease 4       605     t
2006-12-20
\.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux