Hi David,
David Scott wrote:
I am looking at data from a telephone call centre.
I have a table giving data on calls made including time and date with
the name CallDateTime. Each call has a number, CallId and each customer
has a number CustomerNo. Each row represents a different call.
I would like to create a column which identifies the first call made by
a customer in a particular month. That is if a particular call is the
first call made by that customer in that month, there is a 1 in the
column, otherwise there is a zero.
I would also like to identify the second call (if any) made by the
customer in a particular month.
I am quite inexperienced with MySQL and SQL in general and would
appreciate any help which you can offer.
There are many solutions to this type of query. I have written about
some of them at
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
For your particular query, I would start by just writing a query to find
the rows, then progress to maintaining the column later. Probably
something like this would do it:
select calls.* from calls
inner join (
select CustomerNo, min(CallDateTime) as CallDateTime
from calls
group by CustomerNo, left(CallDateTime, 7)
) as min_rows using(CustomerNo, CallDateTime)
This may not be the most efficient way to do the query, but I think once
you learn how it works you can worry about that. (Only you can do that,
because you know the table structure and the kinds of queries you're doing).
Finding the second call is just an extension of this technique. More is
in the article I linked.
cheers
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
