Posted by ice on 12/27/06 19:01

Erland Sommarskog wrote:
> ice (iceruam@gmail.com) writes:
> > 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.
> > Visit Date Called Date
> > ClientA 2006-11-02 2006-12-16
> > ClientB 2006-12-17 2006-10-30
> > ClientC 2006-10-15 2006-10-16
> > ClientD
> >
> > Fields (Simplified)
> > Clients: Name, Address, Phone.
> > Contacted: Name, Date, Visit, Call.
> > I need to query all l names, but I only need the last visit and last
> > phone call. 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.
>
> It's a good recommendation for this type of queries to post CREATE
> TABLE statements for your tables, and INSERT statements with sample
> data, and the desired output given the sample. That makes it easy to
> copy and paste to develop a tested solution. The sample data can also
> help to clarify the narrative. The below is thus untested and based
> on my understanding of your description.
>
> SELECT Cl.Name, V.Date, C.Date
> FROM Clients Cl
> LEFT JOIN (SELECT Name, Date = MAX(Date)
> FROM Contacted
> WHERE Visit = 1
> GROUP BY Contaced) AS V ON V.Name = Cl.Name
> LEFT JOIN (SELECT Name, Date = MAX(Date)
> FROM Contacted
> WHERE Call = 1
> GROUP BY Contaced) AS C ON C.Name = Cl.Name
> WHERE V.Date < datedadd(day, -30, getdate()) OR V.Date IS NULL
>
> The things in parens are derived tables. Conceptually a temp table
> in the query, but not materialised, and SQL Server may recast computation
> order, as long as the result is the same. This makes derived tables a
> very powerful features to implement complex queries.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


I was not able to make it work, getting an a error about a relation.*


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
\.

[Back to original message]