Monsterous SQL Query... Suggestions?

Hi All,

I work for a telco. We've got a table in a database which shows phone calls made by customers and when they made them.

I need to generate a list of customers who have made phonecalls last month and have NOT had a five days in a row without making any calls.

Can any of you help? I'm not sure how to tackle this one without getting a very bloated and inelligent solution. Basically, the only solution I can think of is generating 31 tables, one for each day and then just checking calls made on each day.

Has anyone got a better idea?

I'm using SQL Server 2000.

Thanks

Dave

For the love of sweet Sophia, DON'T DO THAT!

All you need is one table:


Your database software should have a DateDiff function or something similar.

You can generate a list of calls in the last month with a very simple query:


The other part of your query, "have NOT had a five days in a row without making any calls" is probably not possible to do in SQL. As soon as you start writing queries that don't fit into a relational model, or can't be drawn up in a relational diagram, then your query is probably doing too much.

The most reasonable solution I can think of is creating a seperate table, something that would hold the most frequent calls in a month:


- each time a customer makes a call, look up their last call and calculate the duration in days between their two calls. Update the CallFrequency table if needed.

I'd consider that a hackish solution, but its a lot more reasonable than 31 tables, and its super easy to join on and get the data you want.

Thank,

Aye, I though it would be a pain in rear. It's not a relational thing. but it is necessary thanks to a contract agreed by some non-IT peoples. I'm thinking some procedural development will be needed.

Thanks again.

it most certainly is possible

I can think of a way to do it, but it's not exactly pretty and it's not set theory.

Can you think of a way?

yes, i sure can

Please will you tell me?

i would be happy to

would you kindly show me your table layout, give some meaningful sample rows, and show what output you expect from the sample data

First create a table Callday (fields CallDate and Customer) with a record for every day the customer made at least one call. For calculation of the first and last five days of the month, presume they have called on the last day of previous month and the first day of the next month. In code:
select distinct custmerID, convert(char(8), Date,112) from CallTable where Month(Date) = @thismonth
union
select distinct customerID, dateadd(d, -1, convert(smalldatetime, '2008' + @thismonth + '01') from CallTable
union
select distinct customerID, convert(smalldatetime, '2008' + @nextmonth + '01') from CallTable

Next, create a table CallInterval with for every day a customer called, the next day the same customer made a call.

select customerID, c1.date as day1, min(c2.date) as day2
into CallInterval
from CallDay c1
join CallDay c2 on c1.customerID=c2.customerID and c1.date < c2.date
group by customerID, c1.date

To find the customers who have not five days in a row without making a call:
select distinct customerID
from CallInterval
where customerID not in
(
select customerID
from CallInterval
where Day1 + 5 < Day2
)

piece of cake!

Aye.

It's a table with the following columns

CallID PK
TelephoneNumber
StartDate
Duration

I was going to ignore the duration column and just take the StartDate as the time the call was made and not calculate is a call has gone from one day to the next.

Thanks

okay, table layout looks workable

how about those sample rows please?

They're just what you'd expect.

CallID PK
TelephoneNumber
StartDate
Duration


CallID = integer
TelephoneNumber = varchar(11) e.g. 01135487625
StartDate = SmallDateTime e.g. '01/12/2007 10:30:00'
Duration = int (Number of minutes on call)

Does this help? It's a simple table.

no, i meant actual sample rows, so that i could use them to create a test table over here, so that i would have something to test the query on that i was going to write for you

ah, never mind, use marco's

Ah, well, if you are still willing then I'll send it, but in what format do you to use it? XML or csv or the like?

there is only one format that will ensure i don't have to put in too much work --