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
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
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.