473,289 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Selecting last date

ice
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.
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.
Any tips, ideas would be greatly appreciated....
Thanks
Ice

Dec 26 '06 #1
9 16878
ice (ic*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 26 '06 #2
ice

Erland Sommarskog wrote:
ice (ic*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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

Dec 27 '06 #3
ice

Erland Sommarskog wrote:
ice (ic*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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
\.

Dec 27 '06 #4
ice (ic*****@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_TIMESTAMP. 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 27 '06 #5
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
Dec 27 '06 #6
ice

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.

Dec 28 '06 #7
>In ANSI SQL, getdate() should be CURRENT_TIMESTAMP. 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.

Dec 31 '06 #8
--CELKO-- (jc*******@earthlink.net) writes:
>>In ANSI SQL, getdate() should be CURRENT_TIMESTAMP. 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 31 '06 #9
>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.

Jan 3 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Wade Williams | last post by:
Imagine you've got the following situation (Oracle 9i): Table A ------------------- Name Customer Log_Date Log_Type
1
by: Mark Hargreaves | last post by:
Hopefully someone can help. I have two tables, namely master and postings: Fields include the following: master: staffno - int (11) primary key forename - varchar (20) surname - varchar...
1
by: Sunny K | last post by:
Hi, I am having a problem with aquery. Firstly here is a script to create the table and insert some sample data: CREATE TABLE . ( (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , ...
1
by: Finlay | last post by:
Hi Group I am designing a report that should show a meter reading for each month and the previous meter reading for the previous month. The months are text stored in a field tMonth. The...
3
by: larry mckay | last post by:
anyone have the code to select and listview item or row (subitems) after a doubleclick event from a listview. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate...
0
by: Gordon Youd | last post by:
Hi, the group helped me with my last post "Select query from query1 or query2". How do I show " JourneyBookingFormQuery.Req ArrTime" on the "Out column in sortkey" but not in "In column in...
1
by: vunderusaf | last post by:
I have a listbox on a form that is selecting using named FinalQuery: SELECT ., ., . FROM FinalQuery; Now I have a text field with a date on this form and I'd like to use that date as the...
4
by: wishwish20 | last post by:
Hello, I am having trouble with an SQL query which is used as a source on a report. The database is being used to keep track of billing information. I have made an sql query, which has 2 tables....
2
by: n4nz | last post by:
I have a table in oracle that contains transaction data by item part number. This means for each part number there will be more than one transaction date, what i am trying to extract is the last...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.