March 24, 2011 at 9:26 am
I have a temp table that has millions of records. Some of these records would be the same call that I need to count as one record. Each time a new customer id is present it will be a new call. I need to some in intervals of 15 minutes. My expected results are:
The results expected from the Query
CustomerID Interval Calls
122011-03-04 1:00:00.000 2
232011-03-04 1:00:00.000 1
122011-03-04 1:15:00.000 1
This is an example of the data:
Create Table #PhoneCalls(CustomerID Int,PhoneTime DateTime,ST varchar(4))
Insert #PhoneCalls(CustomerID,PhoneTime,ST)
Select 12,'20110304 1:05:00','WI' Union all
Select 12,'20110304 1:05:00','WI' Union all
Select 23,'20110304 1:06:00','WI' Union all
Select 23,'20110304 1:06:00','WI' Union all
Select 23,'20110304 1:06:00','IS' Union all
Select 12,'20110304 1:08:00','WI' Union all
Select 12,'20110304 1:08:00','WI' Union all
Select 12,'20110304 1:08:00','WI' Union all
Select 12,'20110304 1:12:00','WI' Union all
Select 12,'20110304 1:12:00','WI' Union all
Select 12,'20110304 1:12:00','WI' Union all
Select 12,'20110304 1:14:00','GA' Union all
Select 12,'20110304 1:14:00','WI' Union all
Select 12,'20110304 1:14:00','MI' Union all
Select 12,'20110304 1:14:00','MI' Union all
Select 12,'20110304 1:17:00','GA' Union all
Select 12,'20110304 1:17:00','WI' Union all
Select 12,'20110304 1:17:00','GA'
I have had a hard time with getting the count right any help would be appreciated! If I have over looked something let me know. This is what I have come up with so far:
SELECT r.CustomerID,r.g AS Interval, COUNT( r.CustomerID|CAST(r.g AS int) ) AS Calls FROM (
SELECT CustomerID, PhoneTime, dateadd(minute, datediff(n, 15, PhoneTime) / 15 * 15, 0)AS g
FROM #PhoneCalls
GROUP BY CustomerID,PhoneTime,dateadd(minute, datediff(n, 15, PhoneTime) / 15 * 15, 0)
) AS r
GROUP BY r.CustomerID,r.g
March 24, 2011 at 12:57 pm
I think what you need is to aggregate over a partition based on the 15 minute interval. See if something along these lines works for you:
WITH cte
AS (SELECT CustomerID,
-- rounds time down to the nearest quarter hour as a smalldatetime
CONVERT(SMALLDATETIME, CAST(FLOOR(CAST(PhoneTime AS FLOAT) * 24 * 4) / (24 * 4) AS DATETIME)) AS RoundedPhoneTime
FROM #PhoneCalls
)
SELECT DISTINCT
CustomerID,
RoundedPhoneTime,
COUNT(*) OVER (PARTITION BY CustomerID, RoundedPhoneTime) AS calls
FROM cte
ORDER BY CustomerID,
RoundedPhoneTime ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 24, 2011 at 1:18 pm
That is along the same results I am getting. If you notice I gave the expected results. The break down is that the records I will try and explain.
These 2 will count as 1 and the next the customer id changes it begins a new call
Select 12,'20110304 1:05:00','WI' Union all
Select 12,'20110304 1:05:00','WI' Union all
These 3 will count as 1 and the next the customer id changes it begins a new call
Select 23,'20110304 1:06:00','WI' Union all
Select 23,'20110304 1:06:00','WI' Union all
Select 23,'20110304 1:06:00','IS' Union all
These 10 will count as 1 and the next the customer id changes it begins a new call
Select 12,'20110304 1:08:00','WI' Union all
Select 12,'20110304 1:08:00','WI' Union all
Select 12,'20110304 1:08:00','WI' Union all
Select 12,'20110304 1:12:00','WI' Union all
Select 12,'20110304 1:12:00','WI' Union all
Select 12,'20110304 1:12:00','WI' Union all
Select 12,'20110304 1:14:00','GA' Union all
Select 12,'20110304 1:14:00','WI' Union all
Select 12,'20110304 1:14:00','MI' Union all
Select 12,'20110304 1:14:00','MI' Union all
These 3 will count as 1 and the next the Time changes it begins a new call
Select 12,'20110304 1:17:00','GA' Union all
Select 12,'20110304 1:17:00','WI' Union all
Select 12,'20110304 1:17:00','GA'
I hope this gives better understanding of how I am counting.
March 24, 2011 at 1:48 pm
Here's a slightly more complicated solution:
; WITH cte1 AS
(
SELECT
*,
DATEADD(mi,DATEPART(MINUTE,phonetime)/15 *15,DATEADD(hh,DATEDIFF(hh,0,Phonetime),0)) AS INTERVAL
FROM #PhoneCalls
), cte2 AS
(
SELECT
customerid,
INTERVAL,
ROW_NUMBER() OVER(PARTITION BY INTERVAL ORDER BY phonetime)-
ROW_NUMBER() OVER(PARTITION BY INTERVAL,customerid ORDER BY phonetime) AS number
FROM cte1
)
SELECT
customerid,
INTERVAL,
COUNT(DISTINCT number) AS calls
FROM cte2
GROUP BY customerid, INTERVAL
Cte1 is used to add a column with the related 15 minute interval. This is done by "normalizing" the value of Phonetime to the hour and add the 15min interval based on the minute part of the value divided by 15 leading to an integer between 0 and 3. This value then is used to multiply by 15 and add it back to the "hour-normalized" time. You might want to brak this rather long DATEADD/DATEDIFF/DATEPART section apart to see how it works...
The second cte (cte2) assigns a group value to each customerid. The group value will be identical as long as it is within the same 15min interval and as long as there is no other phone call in between. Again, break the ROW_NUMBER statement apart to see how it works.
March 24, 2011 at 1:51 pm
Dang it Lutz...I was almost there! You got it!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 24, 2011 at 1:54 pm
Thanks LutzM thats what I was looking for.
March 24, 2011 at 2:27 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply