Selecting last date

**Erland Sommarskog** · Dec 26 '06, 04:55 PM

Re: Selecting last date

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@sommarsk og.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

**ice** · Dec 27 '06, 12:55 PM

Re: Selecting last date

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@sommarsk og.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/prodinf...ons/books.mspx

THANKS, I will give this a go.
ICE

**ice** · Dec 27 '06, 07:05 PM

Re: Selecting last date

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@sommarsk og.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/prodinf...ons/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
\.

**Erland Sommarskog** · Dec 27 '06, 10:55 PM

Re: Selecting last date

ice (iceruam@gmail. com) writes:

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

Seeing your tables and your COPY commands, it's apparent to me that
whatever you are using, it is not Microsoft SQL Server. The solution
I posted is almost ANSI-compliant. The exception is the expression:

datedadd(day, -30, getdate())

In ANSI SQL, getdate() should be CURRENT_TIMESTA MP. Whether dateadd (my
query mistakely had "datedadd" I see now) is in ANSI SQL, I don't know,
nor do I know about date aritmethics in general in ANSI SQL.

Thus, theoretically, beside this expression, the solution should work
if your DB engine implements the same ANSI constructs as SQL Server does.

Alas, just because a query is ANSI-compliant, does not mean that it
will run all engines, so you may have to use a solution that uses
syntax peculiar to the product that you work with. If you need help
with that, you will have to find a forum for your product. If that
product is mysql, there is a comp.databases. mysql nextdoors from here.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

**Hugo Kornelis** · Dec 27 '06, 11:05 PM

Re: Selecting last date

On 27 Dec 2006 11:01:28 -0800, ice wrote:

(snip)

>I was not able to make it work, getting an a error about a relation.*
>
>
>CREATE TABLE contacted (

(snip)

>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

(snip)

Hi ice,

Thanks for posting the table structures. The first thing I noticed is
that some of the datatypes used are not valid on MS SQL Server. You are
apparently using some other DBMS. You might get better help when posting
in a newsgroup for your DBMS!

That being said, I am willing to look into your problem - but only if
you can post the data as INSERT statements (the COPY statement you used
is not supported on MS SQL Server either), *and* if you post the exact
and complete error message you got (use copy and paste if possible).

--
Hugo Kornelis, SQL Server MVP

**ice** · Dec 28 '06, 01:05 PM

Re: Selecting last date

Hugo Kornelis wrote:

On 27 Dec 2006 11:01:28 -0800, ice wrote:
>
(snip)

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

CREATE TABLE contacted (

(snip)

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

(snip)
>
Hi ice,
>
Thanks for posting the table structures. The first thing I noticed is
that some of the datatypes used are not valid on MS SQL Server. You are
apparently using some other DBMS. You might get better help when posting
in a newsgroup for your DBMS!
>
That being said, I am willing to look into your problem - but only if
you can post the data as INSERT statements (the COPY statement you used
is not supported on MS SQL Server either), *and* if you post the exact
and complete error message you got (use copy and paste if possible).
>
--
Hugo Kornelis, SQL Server MVP

OK.
Thanks.

**--CELKO--** · Dec 31 '06, 02:05 PM

Re: Selecting last date

>In ANSI SQL, getdate() should be CURRENT_TIMESTA MP. Whether dateadd (my query mistakely had "datedadd" I see now) is in ANSI SQL, I don't know, nor do I know about date aritmethics in general in ANSI SQL. <<

It is not ANSI/ISO. Temporal math in Standard has infixed operators +
and - with temporal unit declarations, EXTRACT() and oher functions
that are very different from the Sybase/SQL Server "code museum"
function calls. DB2 and Oracle 10 now both have the proper syntax. I
thnk that Mimer and Solid are also up to standards.

**Erland Sommarskog** · Dec 31 '06, 02:55 PM

Re: Selecting last date

--CELKO-- (jcelko212@eart hlink.net) writes:

>>In ANSI SQL, getdate() should be CURRENT_TIMESTA MP. Whether dateadd (my

query mistakely had "datedadd" I see now) is in ANSI SQL, I don't know, nor
do I know about date aritmethics in general in ANSI SQL. <<

>
It is not ANSI/ISO. Temporal math in Standard has infixed operators +
and - with temporal unit declarations, EXTRACT() and oher functions
that are very different from the Sybase/SQL Server "code museum"
function calls. DB2 and Oracle 10 now both have the proper syntax. I
thnk that Mimer and Solid are also up to standards.

SQL Server also has +/- for datetime, although I suspect this is due
to implicit conversion. Look at this:

declare @d1 datetime, @d2 datetime
select @d1 = '19820223 16:21:32', @d2 = '19871031 12:23:23'
select @d1 + 1, @d2 - @d1

Gives:

1982-02-24 16:21:32.000 1905-09-07 20:01:51.000

The first makes perfect sense, the second is just rubbish.

The dateadd() etc stuff is not that pretty, but the get the job done.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

**--CELKO--** · Jan 3 '07, 04:15 PM

Re: Selecting last date

>SQL Server also has +/- for datetime, although I suspect this is due to implicit conversion. <<

The ANSI/ISO version has to have temporal units with the +/- like
this:

(my_date + INTERVAL 2 DAYS)

This makes more sense and avoids the rubbish.