Repeat Caller Query

Last Post 04 Oct 2008 10:09 AM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
CarpenterA
New Member
New Member

--
03 Oct 2008 12:25 AM
Guys,

I have a problem and I'm not sure of the best solution so I'm hoping someone can give me a steer.

I need to provide some repeat caller analysis for a call centre. I have a table containing every call presented to the centre, it contains among other things:

[Calling Line Identity] varchar(11) - the callers telephone number
[Date and Time] datetime - of the call
[Number dialled] varchar(11) - the number the caller dialled to get to the call centre

I need to produce some output that looks like this:

Date, One, Two, Three, Four, Five, More than Five
09/01/2008, 12365, 476, 242, 130, 67, 18
09/02/2088, 10250, 452, 220, 101, 35, 25

It needs to show, for each day, how many times callers that called on that day have called in the previous 7-days.

I would be grateful if someone could post a possible solution or point me the direction of any resources that explain the principles.

Many thanks in advance.
nosepicker
New Member
New Member

--
04 Oct 2008 10:09 AM
This is what I've come up with so far, based on my understanding of your needs:

quote:


SELECT
today.[date],
SUM(CASE WHEN [7days].cnt = 1 THEN 1 ELSE 0 END) AS One,
SUM(CASE WHEN [7days].cnt = 2 THEN 1 ELSE 0 END) AS Two,
SUM(CASE WHEN [7days].cnt = 3 THEN 1 ELSE 0 END) AS Three,
SUM(CASE WHEN [7days].cnt = 4 THEN 1 ELSE 0 END) AS Four,
SUM(CASE WHEN [7days].cnt = 5 THEN 1 ELSE 0 END) AS Five,
SUM(CASE WHEN [7days].cnt > 5 THEN 1 ELSE 0 END) AS [More than Five]
FROM

(SELECT DISTINCT DATEADD(day, DATEDIFF(day, 0, [Date and Time]), 0) AS [date], [Calling Line Identity], [Number dialled]
FROM YourTable) AS today

JOIN

(SELECT [Calling Line Identity], [Number dialled], COUNT(*) AS cnt
FROM YourTable
WHERE [Date and Time] >= DATEADD(day, DATEDIFF(day, 0, getdate()), 0) -8
AND [Date and Time] < DATEADD(day, DATEDIFF(day, 0, getdate()), 0) -1
GROUP BY [Calling Line Identity], [Number dialled]) AS [7days]

ON today.[Calling Line Identity] = [7days].[Calling Line Identity]
AND today.[Number dialled] = [7days].[Number dialled]

GROUP BY today.[date]
ORDER BY today.[date]


You are not authorized to post a reply.

Acceptable Use Policy