473,888 Members | 1,564 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 16943
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****@sommarsk og.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****@sommarsk og.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****@sommarsk og.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_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, es****@sommarsk og.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_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.

Dec 31 '06 #8
--CELKO-- (jc*******@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, es****@sommarsk og.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
2392
by: Wade Williams | last post by:
Imagine you've got the following situation (Oracle 9i): Table A ------------------- Name Customer Log_Date Log_Type
1
2101
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 (30)
1
6766
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 , NOT NULL , NOT NULL
1
5256
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 current month is easy to pull out of the data base but I don't know how to get the previous months meter reading. The previous reading is subtracted from the current reading to get the
3
15724
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 in USENET...get rewarded for it!
0
1135
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 sortkey" ? SELECT JourneyBookingFormQuery.ID, JourneyBookingFormQuery.Prefix, JourneyBookingFormQuery.Surname, JourneyBookingFormQuery.Town, JourneyBookingFormQuery.Req ArrTime, JourneyBookingFormQuery.InwardArrivalTime, IIf(=,'Out','In') AS sortkey
1
1905
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 selection criteria, comparing to the ... how would I use a field with the WHERE clause?
4
2483
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. table A contains customer information. table B contains all the bills for each customer. Table B has fields: customerID, Rate, and the Date. I have a field in the customer table (table A) which contains how many months that they still owe for. I need a...
2
6751
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 transaction date for each part number in a driving table. so.. I have created a table that has 1 column named PART_NO, with numerous part numbers contained in it, i want to left join this to my transaction table and return the last transaction for...
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11180
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10885
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7990
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4244
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.