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 --- ---
22006-11-01
32006-11-01
52006-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 CoSmallville Rd Bigton NY
12234 t 1
Doe JaneDust Grabber Inc10 Dirt Drive Dustin
PA 12345-1222 t 2
Smacher Frank Woodwerkers Inc 100 Forest
LaneOakland CA 12346- t 3
Zimbob Roger Drywallz Inc1 Gympsum Place Quarryville
NY 12347
t 4
Deckem WillPorches are us 2 Backyard Lane Gazeboton
CO 12348 t 5
Crimp GregKidocker2 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
disaster4 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
\.
---(end of broadcast)---
TIP 6: explain analyze is your friend
