April 11, 2011 at 1:58 pm
This may have been covered at some point, but it's a difficult thing to search for while trying to word the question the same way someone else might have, so appologies for what is most likely a repeat.
I have a table of data that shows when customers have called in. The table includes their phone number and the date/time they called.
Here's what I need to do:
I need to extract out the phone number when the customer has called us 3 or more times in a 4-day period, and perhaps include the minimum and maximum call date. The tricky (for me) part is that the call data may overlap at different times.
For example, if a customer calls on 4/10, I would need to include any calls within 4 days, whether the other calls came before 4/10 or after 4/10. But a call COULD be counted in two data sets for the same customer if the call records are far enough apart to not fall within 4 days of another day. Confusing, I know.
Example:
PHONE_NBR CALL_DATE
3215551234 04/10/11
3215551234 04/11/11
3215551234 04/11/11
3215551234 04/14/11
3215551234 04/21/11
3215551234 04/30/11
In the example data above, calls from 4/10 and the two calls from 4/11 would need to show up as all coming in within 4 days of 4/10 (the minimum call date for them), and the two calls from 4/11 would also be included in a second set along with the call from 4/14 because they all happened within 4 days of eachother. The 4/21 and 4/30 calls would be ignored because they did not have a total count of 3 or more calls within 4 days.
The data to be returned by this example would need to be:
PHONE_NBR CALL_DATE CALL_COUNT
3215551234 04/10/11 3
3215551234 04/11/11 3
I hope I did a good enough job of explaining this.
April 11, 2011 at 2:12 pm
How about .....
declare @t table (PHONE_NBR varchar(12), CALL_DATE datetime)
insert @t
select '3215551234','04/10/11' union all
select '3215551234', '04/11/11' union all
select '3215551234', '04/11/11' union all
select '3215551234', '04/14/11' union all
select '3215551234' ,'04/21/11' union all
select '3215551234', '04/27/11' union all
select '3215551234' ,'04/28/11' union all
select '3215551234', '04/29/11'
select distinct
a.phone_nbr
,a.call_date
from @t as a
cross apply (select i.phone_nbr, count(i.call_date) as call_count
from @t as i
where i.call_Date between a.call_date and dateadd(day, 4, a.call_date)
group by i.phone_nbr
having count(*) >= 3) as x
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 11, 2011 at 2:12 pm
Pseudocode since you did provide an example but not consumable sample data (see my sig, first link, if you need help with that):
SELECT
t1.Field,
CallCount
FROM
table AS t1
CROSS APPLY ( SELECT count(*) AS CallCount FROM table AS t2 WHERE t2.Calldate BETWEEN t1.CallDate AND DATEADD( dd, 4, t1.Calldate)) AS drv
WHERE
drv.Count >=3
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 12, 2011 at 8:36 am
I received a minor change to the requirements and have tried to simplify this as a result.
I created a new table that only has 3 columns in it:
PHONE_NBR (self-explanatory)
CALL_DATE (date the customer called)
CALL_COUNT (number of calls received on the call_date)
Now what I need to get to is a list of the PHONE_NBR's that had >=3 calls within a 4-day timeframe.
Hopefully this will make it easier to extract.
April 12, 2011 at 10:48 am
Seems like this isn't pulling back records as intended. Every phone number came back showing '3' calls.
April 12, 2011 at 10:56 am
How would your suggestion work with the updated table structure I posted?
Also, the table has ~6M rows in it, so I can't really input the data the way you showed for the temp table. I tried selecting the rows instead, but not working.
For example, I have a phone number to test with that I know called 3x within 4 days and they are not returned with the query.
April 12, 2011 at 12:19 pm
Mr Corn Man (4/12/2011)
How would your suggestion work with the updated table structure I posted?Also, the table has ~6M rows in it, so I can't really input the data the way you showed for the temp table. I tried selecting the rows instead, but not working.
For example, I have a phone number to test with that I know called 3x within 4 days and they are not returned with the query.
I believe this is what you're looking for ...
declare @t table (PHONE_NBR varchar(12), CALL_DATE datetime)
insert @t
select '3215551234','04/08/11' union all
select '3215551234', '04/11/11' union all
select '3215551234', '04/11/11' union all
select '3215551234', '04/12/11' union all
select '0123456789', '04/14/11' union all
select '0123456789' ,'04/21/11' union all
select '1112223333', '04/27/11' union all
select '1112223333' ,'04/28/11' union all
select '1112223333', '04/29/11' union all
select '3215551234', '05/11/11'
---------------------------use the code below, just replace @t with your table name
select distinct
z.phone_nbr
,min(z.call_date) as call_date
,count(*) as call_count
from
@t as z
inner join (select distinct
a.phone_nbr
,a.call_date
from @t as a
cross apply (select i.phone_nbr
from @t as i
where i.call_Date between a.call_date and dateadd(day, 4, a.call_date)
group by i.phone_nbr
having count(*) >= 3) as x) as y
on z.phone_nbr = y.phone_nbr
and z.call_date between y.call_date and dateadd(day, 4, y.call_date)
group by z.phone_nbr,y.call_date,z.call_date
If this is not what you want, then post some sample data for numbers that you know should appear. A small subset of the table is sufficient.
** Please note, I'm not 100% sure this is the best way, but it does work. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 12, 2011 at 12:49 pm
I tested your updated suggestion with a single phone number as a sample.
With the new structure I mentioned, the source table now has the following records for this particular test phone number:
PHONE_NBR CALL_DATE CALL_COUNT
5551234567 3/15/11 1
5551234567 3/17/11 2
When I ran your query, I got the following results:
PHONE_NBR CALL_DATE CALL_COUNT
5551234567 3/15/11 1
5551234567 3/17/11 1
Here is what I want to get back:
PHONE_NBR CALL_DATE CALL_COUNT
5551234567 3/15/11 3
The desired results will tell me the phone number, call date for the 1st call, total calls within 4 days of the 1st call.
I still only want to include those that had >=3 calls within 4 days.
Hope this makes more sense, thanks!
April 12, 2011 at 1:03 pm
I guess I should have clarified again that if there are records that fall before/after the 4-day window that 3 or more calls occurred in, they would not be included in the results.
Sample table records:
PHONE_NBR CALL_DATE CALL_COUNT
5551234567 3/5/11 1
5551234567 3/11/11 1
5551234567 3/14/11 2
5551234567 3/27/11 1
Using the above sample records, the result included in the output would be:
PHONE_NBR CALL_DATE CALL_COUNT
5551234567 3/11/11 3
So 3/11/11 was the initial call date that had 3 or more calls within the 4 days (1 on 3/11 and 2 on 3/14 for a total of 3). The call on 3/5 and the call on 3/27 would not be included because 3 or more calls did not occur within 4 days of them.
Geez...sorry for so many clarifications!
April 12, 2011 at 1:13 pm
Mr Corn Man (4/12/2011)
I guess I should have clarified again that if there are records that fall before/after the 4-day window that 3 or more calls occurred in, they would not be included in the results.Sample table records:
PHONE_NBR CALL_DATE CALL_COUNT
5551234567 3/5/11 1
5551234567 3/11/11 1
5551234567 3/14/11 2
5551234567 3/27/11 1
Using the above sample records, the result included in the output would be:
PHONE_NBR CALL_DATE CALL_COUNT
5551234567 3/11/11 3
So 3/11/11 was the initial call date that had 3 or more calls within the 4 days (1 on 3/11 and 2 on 3/14 for a total of 3). The call on 3/5 and the call on 3/27 would not be included because 3 or more calls did not occur within 4 days of them.
Geez...sorry for so many clarifications!
Sorry, but I have to bow out now. The code I Posted Yesterday @ 3:12 PM is very close to what you need. Try fiddling with that to see if you can get it to work. I'll try to check back later to see how you've done, so don't forget to post when/if you get it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply