List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:June 3 2007 5:01pm
Subject:Re: Obtaining the first or second instance of an event
View as plain text  
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
Thread
Obtaining the first or second instance of an eventDavid Scott3 Jun
  • Re: Obtaining the first or second instance of an eventBaron Schwartz3 Jun