Help: Repeat Call Metric creation

1

I'm new to SQL and using SQL Server 2012.

I need to count how many times a phone number called back into our call centers after their first call, within 24/48/168 hours, and tag that to the agent who took the first call.

The goal is to identify agents who generate a lot of call backs so we can prioritize them for training programs.

My output should eventually have a Month To Date metric that looks like this:
Agent Name | 24 hour repeats | 48 hour repeats | 168 hour repeats
Minnie Mouse| 10% | 12% | 20%

Additional info: I've tried this in Excel and Tableau. I've successfully formulated this in Excel, but the amount of data can't be handled in Excel and I've been directed to do this directly in the database. We have ~10k rows of data added per day, and this would need to be perpetual to tie into a live Tableau dashboard. Tableau (according to developers I've spoken with) cannot calculate on this data in this way. They, too, have directed me back to SQL.

2

please provide sample mickey mouse family data as follows

create table #mice(AgentName varchar(50), CallDate datetime )
insert into #mice
select 'Minnie Mouse', getdate()

etc

Meeska, Mooska, Mickey Mouse!

3

Here's what I've started with. It's not exactly what I need, though it does give me some pretty great Line Of Business level graphs:

SELECT
EXTRACT( YEAR
FROM
datetimeadded) AS Year,
EXTRACT( Month
FROM
datetimeadded) AS Month,
A.lineofbusiness,
lastlocalname,
remotename,
remotenumberfmt,
COUNT(b.remotenumberfmt) AS RepeatCount
FROM
discovery02.acgcustomcalllog AS A
INNER JOIN
discovery02.interactionsummary AS B
ON
a.interactionidkey = b.interactionidkey
WHERE
a.datetimeadded BETWEEN '2019-12-01 00:00:00'
AND '2019-12-31 00:00:00'
AND b.lastlocaluserID <> ''
AND b.lastlocalname IS NOT NULL
AND lineofbusiness <> ''
AND lineofbusiness IS NOT NULL
GROUP BY
Year,
Month,
lineofbusiness,
lastlocalname,
remotename,
remotenumberfmt

Details:
lastlocalname = The agent anme
remotenumberfmt = The caller's phone number
remotename = The caller ID info (customer name, location, business name, etc.)

We have thousands of agents and this would need to work without updating new hires or terminations.

Is this what you were hoping for?

4

hi

i am trying to create SAMPLE Data Script

Based on his select query !!! i have come up with the following
Whats remaining is the insert data part !!

drop table acgcustomcalllog
go 

create table acgcustomcalllog
(
lineofbusiness varchar(100),
interactionidkey int, 
datetimeadded date
)
go 

insert into acgcustomcalllog select 'OOPs',1,'2019-10-09'
select 'acgcustomcalllog sample data',* from acgcustomcalllog
go 


drop table interactionsummary
go 

create table interactionsummary
(
remotenumberfmt varchar(100),
interactionidkey int,
lastlocaluserID int ,
lastlocalname varchar(100)
)
go 

insert into interactionsummary select 'samsung',1,123,'denver'
select 'interactionsummary sample data',* from interactionsummary
go

image
image969×103 5.1 KB

image
image1173×119 4.81 KB

5

this does not sound like SQL Server?

EXTRACT( YEAR
FROM
datetimeadded) AS Year
6

Sorry... That was from the MySQL code. It isn't necessary since Tableau can extract and separate YY MM DD, which is where this will eventually live.

This code didn't give me exactly what I was hoping for anyway, so if you can think of something better, I'd be very appreciative. I've tried a ton of different variations, but I'm very new to SQL as a whole. People have suggested types of syntax, and I've just used sites w3schools to try to figure it out.

7

Could you please give me direct access to your SQL Server?

1 Like
8

No, It's all proprietary

9

In that case please provide sample data as requested in previous posts.

sample for acgcustomcalllog
sample for interactionsummary

Help us help you.

10

Ah, I see what you mean now. Thanks! I'll upload shortly.

1 Like
11

Forgive my noob-ness, please! I tried to run the script you had to see if I could get what you were looking for, and I got this: (1 row(s) affected)
I'm not sure what you mean by data family. I have the tables acgcustomcalllog and interactionsummary with 64 columns each. This site doesn't let me attach a csv for example data of that. I don't have a list of all agent names, and we have thousands of them... I do know we don't hold hierarchy data in this server, though. Also, I wouldn't want to specify names if it would require manual changes to the code to be done as new hires and terms happen.
I'm sorry, I really feel stupid here...

12

No problem. We just need a sampling of data not 10k of it. So please Provide the data using dml and ddl. What are those? Google them and respond back here. This is part of your free training.
We cannot work with csv because we have developers here that would have to parse the csv file, generate dml and ddl to emulate your data locally in our own SQL server database.
Folks are busy and might not have time to do that for you. So you need to provide that sample data as dml and ddl. We could generate this data locally but it would be random guess data. Then we post a reply to you with that bogus random data and then you might respond saying the results are wrong and so on and so forth. To avoid this, you need to provide us with near real sampling of your data

1 Like
13

Got it! Thank you so much for that thoughtful response! I'll work on the ddl/dml and post back.

Thanks again!

14

using sample data, others might have a better solution. at least we now have sample data.

use SQLTeam
go

create table #acgcustomcalllog
(
	lineofbusiness varchar(100),
	interactionidkey int, 
	datetimeadded datetime
)
go 
/*
*/
insert into #acgcustomcalllog(lineofbusiness, interactionidkey, datetimeadded)
select 'Robot Recycling', 1, dateadd(hour,2^1,getdate()) union
select 'Robot Recycling', 2, dateadd(hour,2^2,getdate()) union
select 'Robot Recycling', 3, dateadd(hour,2^3,getdate()) union
select 'Robot Recycling', 4, dateadd(hour,2^4,getdate()) union
select 'Robot Recycling', 5, dateadd(hour,2^5,getdate()) union
select 'Robot Recycling', 6, dateadd(hour,2^6,getdate()) 

create table #interactionsummary
(
	remotenumberfmt varchar(100),
	interactionidkey int,
	lastlocaluserID int ,
	lastlocalname varchar(100)
)
go 

--select * from  #acgcustomcalllog
insert into #interactionsummary(remotenumberfmt, interactionidkey, lastlocalname, lastlocaluserID)
select '104-104-' + cast(interactionidkey as varchar(150)), interactionidkey, 'Darth Agent', 1 from  #acgcustomcalllog


select _24hours.lastlocalname, _24hours._hoursWorked , 
      th._hoursCallingOneNumber,
      ( (th._hoursCallingOneNumber*1.00)/ (_24hours._hoursWorked*1.00) ) *100 _percentage,
	  remotenumberfmt
 from  (
			select lastlocalname,  sum(DateDiff(hour, getdate(), datetimeadded)) _hoursWorked
			from #acgcustomcalllog a 
			join #interactionsummary b on a.interactionidkey = b.interactionidkey 
			where DateDiff(hour, getdate(), datetimeadded) between 1 and 24
			group by lastlocalname
		) _24hours
	left join (
		select sum(DateDiff(hour, getdate(), datetimeadded)) _hoursCallingOneNumber, lastlocalname, remotenumberfmt
		from #acgcustomcalllog a 
		join #interactionsummary b on a.interactionidkey = b.interactionidkey 
		where DateDiff(hour, getdate(), datetimeadded) between 1 and 24
		group by remotenumberfmt, lastlocalname
	) th on _24hours.lastlocalname = th.lastlocalname
	--join (select distinct lastlocalname, remotenumberfmt from  #interactionsummary) telno on telno.lastlocalname = th.lastlocalname



drop table #interactionsummary
drop table #acgcustomcalllog