Posted by ice on 12/27/06 12:44

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


THANKS, I will give this a go.
ICE

[Back to original message]