|
|
Posted by Erland Sommarskog on 12/26/06 16:48
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
Navigation:
[Reply to this message]
|