Calculate customer habits (SQL) - possible?

pickarooney

I've been given the task of ascertaining the loyalty of customers to a particular employee with the following elements and I'm totally stuck and would like to know if you guys think what I'm trying to do is at least possible before I melt my head any further.

Basically, I've three elements (tables) to work with - customer, ticket, employee - and I have to figure out the following.
The customer table contains the customer id and nothing else really useful for the operation except perhaps total number of visits, but I believe this is useless for the task in hand.
The employee table is the same, only the id is of use.
The ticket table contains its own id, a date, a customer name and an associated employee name.

For a given period, A to B (exclusive), I need to be able to calculate
1) - The number of new customers associated with a particular employee (easy: select count (*) from ticket where employee=1 and date > A and date < B and customer not in (select customer from ticket where date <A))

Note: the dates refer to specific points in time, midnight on the day in question, so there's no issue with A not being included in either comparison.


2) - The number of customers associated with the employee in the period who also were associated with the same employee at least twice in the last three tickets for that customer.

3) - The number of customers associated with the employee in the period who were neither new, nor associated with the same employee at least twice in their last three tickets nor have only two tickets total, both of which feature the employee.


Part 3 will follow on logically from 2, I suppose. The last part of it might be tricky in itself, but if I could get to grips with 2 I think it should be fairly doable. My basic problem is in getting a nested select statement on the same table (ticket) to reuse the same field - I'm not really able to explain this well...

select count(*) from ticket where employee=1 and date > A and date < B and select count(*) from ticket [using row_number to get only the last three tickets maybe?] where customer= [customer from first clause] and employee=[employee from first clause] >1

In that last bit I'm trying somehow to pick up the last three tickets for the customer selected in the first clause and including the customer in the count if the employee's name is on at least 2 of the tickets.

Tom Dunne

What database are you using?

pickarooney

MS SQL 2000

pickarooney

I got a little bit further with this (resolved the nested referencing) but my mind is melted as to why a certain subset of clients comes up as both new and returning clients.

There's another table to consider, ticketline, which contains the information relating to the employee and whose ID is calculated as a point in time during the day which begins at midnight, denoted by the ID of the ticket and ends at midnight, denoted by the following ticket's ID. Tickets can have multiple ticketlines, and not all ticketlines on a ticket have the same employee.


New clients:

select count (distinct ticket) from ticketline TL1 where employee=42 and id > 676900000 and id < 677500000 and customer not in (select customer from ticketline where id < TL1.id)

Returning clients:

select count (distinct ticket) from ticketline TL1 where employee=42 and id > 676900000 and id < 677500000 and customer in (select customer from ticketline where id < TL1.id and TL1.customer = customer)

When I replace the count with a select customer I get six clients appearing on each list.

edit: seem to have resolved it, although I don't understand how the DISTINCT didn't do the same thing... replaced id < TL1.id by id < TL1.ticket and there are no more duplicates.