One query or two?

[GRB]

Hello,



I would like help with a query. Let’s suppose I have a call center that is in constant contact with a group of people offering them a set of services. I call people and ask them if they have a particular service and, if not, would they like to. The core of the database looks like this:

Call_Id unique identifier for the call
Call_TimeStamp date and time of call
CalledPerson_Id unqiue identifer of the person being called
HasService a boolean as to whether they have yet the service

The HasService variable is recorded on each call, including the first.

Is it possible to obtain the number of people who have the service on the first and the number who have it on the second call using a single query?

Hope this question was clear, thanks in advance.


GRB

[Rixxe]

Simple answer = Yes (1) Or 2

If all you are doing is counting:

Call = 1
HasService = 3

Call = 2
HasService = 1

TotalHasService = 4?

If its more complicated, i.e you have to count only the ones on that day, you can use a where DATE1 not <= DATE2.
Search 'Count', 'SUM' Functions...

I guess that's all you need

[GRB]

Hi Rixxe, thanks for getting back.

Let me see if I can clarify the question. Without going into details, we have a health screening service. Everyone gets an initial call and at least one additional followup call.

Initially we ask if they have a particular service, lets say service_x. During each followup we as the same question: do you have service_x?

Is it possible, in one query, to count the number of people who have service_x in the first call and the number of people who have service_x on the followup call?

Thanks again for your help.
GRB

[Rixxe]

Yup that shouldnt be hard:
If you have say, 2 Calls a year (For example).
You find the Minimum date for that person, then count the x_service.
You find the maximum date for that person (followup call) and count the x_service

MIN(), MAX(), COUNT() functions are all you need, you could do an IF exists query, so that if they do not exist you don't count.

Good luck

Rixxe.