Obtaining the first or second instance of an event

2007-06-03 Thread David Scott 


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.


Thanks

David Scott

_
David Scott Department of Statistics, Tamaki Campus
The University of Auckland, PB 92019
Auckland 1142,NEW ZEALAND
Phone: +64 9 373 7599 ext 86830 Fax: +64 9 373 7000
Email:  [EMAIL PROTECTED]

Graduate Officer, Department of Statistics
Director of Consulting, Department of Statistics


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Obtaining the first or second instance of an event

2007-06-03 Thread Baron Schwartz 

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]