Building a table based on another one(s)
-
Hi,
We have 3 people that answer the phones to help our customer with questions. As a result, we get a call report that shows who was on a phone and when, and for how long; which I put in a table; and this report, it also shows missed calls. I made the table below, showing on separate lines, the calls and missed calls that everyone made. AAS we can see here Betty, and Sara was on the phone at roughly the same time and there was a missed call. So based on this we would want to know why Jim did not pick up the phone.
Question: I need to make a table that, using the example, would show.
Answered CallEnded Betty Jim Sara Miss
12:05:34 12:14:30 1 0 1 1
I could make separate tables for each person's calls and a table of Missed calls, but what I am looking to do is
I have been able to get all the data together as follows:
Any ideas would be really appreciated.
Thank you
-
From the tables above, how can you tell that it was Jim who missed the call?
What is the logic behind the Answered and CallEnded grouping window? Where does 12.14:30 come from?
Can you provide sample DDL and data, in the form of INSERT statements, if you want a working solution?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi,
I will try to answer your questions here:
- Based on the times above, from 12:11:02 to 12:14:09, these events occurred (including the missed call); and as can be seen Jim was not on the phone and did not answer it
- Here if you mean the table that I am trying to make? That is the logic I am trying to come up with. basically as you can see in the table I have all the times, and if you search through you can see everything here, but my manager would like to see it all on one line.
- Sorry, your third question I do not understand what you are asking.
Thank you
-
if you can provide your raw data in the format it is on your DB then we can assist more. that said google pivot tables, if all you are doing is aggregating data by time, type and by person
***The first step is always the hardest *******
-
The CallEnded time 12:14:30 appears in your desired output example, but does not appear in the source data. Why is that?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Itzik Ben-Gan did a series of articles on intervals and counts which can be modified to answer your question. Here is the first article in the series. Had you provided consumable data, I would have provided tested code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
This will find who was available to answer missed calls but didn't answer them.
The structure it uses is, I think, a bit more like your raw data must be.
;WITH
Calls AS (SELECT * FROM (VALUES
(CONVERT(time(0),'12:05:34'), CONVERT(time(0),'12:08:02'), CONVERT(int,1)),
('12:11:02', '12:14:07', 3),
('12:11:05', '12:12:30', 1),
('12:11:07', '12:14:09', 0),
('12:14:44', '12:15:42', 1),
('12:17:58', '12:19:18', 1),
('12:21:18', '12:22:24', 1),
('12:21:35', '12:21:47', 2),
('06:56:00', '08:24:00', 1),
('07:45:00', '08:10:00', 2)) T(Answered, CallEnded, StaffId)),
Staff AS (SELECT * FROM (VALUES
(CONVERT(int,0), CONVERT(varchar(20),'Miss')),
(1, 'Betty'),
(2, 'Jim'),
(3, 'Sara')) T(StaffId, Name))
SELECT *
FROM Calls MissedCalls
INNER JOIN Staff s
ON s.StaffId <> 0
WHERE MissedCalls.StaffId = 0
AND NOT EXISTS(SELECT *
FROM Calls Unavailable
WHERE Unavailable.StaffId = s.StaffId
AND Unavailable.Answered < MissedCalls.Answered
AND Unavailable.CallEnded > MissedCalls.Answered) -
Hi,
This looks really good, the one problem is that it looks like I would have to put the time in the CTE manually. Since this is a report that I will need to run every day that could be an issue. Also, I am not sure what times it is. That is is it all the times that users were on the phone or the times call were missed?
Thank you
-
The CTE is just there to represent your tables (I thought you would have a Staff table and a Calls table). You shouldn't have to type anything in manually. I would have the times stored as datetimes, i.e. with the date component with the time.
Have you got a sample of the raw data you have?
-
Hi,
I am not sure that it will help, but here is a sample of the actual raw data. In the data, that I sent you I had a field called Answered () this is the Time field with50 secs. added to it. It has a field called Callended (this is the Time field plus the duration added to it). The Time filed is a Time(4) datatype and the field called answer is the Ids of the employees; except for 700 is missed calls.
<b></b><i></i><u></u><b>Thank you</b>
-
Which column represents the person who answer the call?
How do you calculate the Answered and EndTime?
Where does the hold time come into it?
-
Hi,
Which column represents the person who answer the call?
The column answer holds the person who answered the call (110,105,102 are the people's ID if you will, and could be your staff ID), as well as missed calls (700).
How do you calculate the Answered and EndTime?
Answered is the Time field + 50 secs added to it. And the Callended time is the Time field plus the duration field.
Where does the hold time come into it?
It doesn't come into play for this at all.
-
One other thing just to make this simple as possable.
What would be done manually is that you would find a missed time and see if it is in a time any of the people were on the phone.
If it was you would then put a one under their name so that you would get the following (like I have above):
Answered CallEnded Betty Jim Sara Miss
12:05:34 12:14:09 1 0 1 1
-
You should invest in a call center monitoring package. Perhaps Jim was in the bathroom or at lunch, or working on documentation from a previous call, etc. None of that is accounted for here. Maybe there are multiple phone queues and he wasn't skilled for that queue (perhaps he isn't bi-lingual but the caller needed him to be, etc.)
These problems have already been solved and tested. I would save yourselves some headache and just use one.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses -
This will find members of staff who were not on a call when the missed call started ringing:
Set up data
IF OBJECT_ID('tempdb..#RawData','U') IS NOT NULL DROP TABLE #Calls
GO
SELECT *
INTO #Calls
FROM (VALUES
(
CONVERT(int,8225), -- VaspianCalls_INDEX
CONVERT(date,'06/11/2019'), -- Date
CONVERT(time(0),'12:13:54.0000'), -- Time
CONVERT(varchar,'US\EASTERN'), -- Time_zone
CONVERT(varchar,'INBOUND'), -- Direction
CONVERT(varchar,'9096436039'), -- CallingNumber
CONVERT(varchar,'8008839067'), -- DialedNumber
CONVERT(int,700), -- AutoAttendant
CONVERT(time(0),'00:01:48.0000'), -- Duration
CONVERT(time(0),'00:01:48.0000'), -- Talk
CONVERT(int,606), -- Target
CONVERT(int,110), -- answer
CONVERT(int,NULL), -- account
CONVERT(varchar(200),NULL), -- comments
CONVERT(varchar,NULL), -- Missed
CONVERT(int, 58)), -- Hold_Time
(8227, '06/11/2019', '12:17:08.0000', 'US\EASTERN', 'INBOUND', '7135849693', '8008839067', 700, '00:02:10.0000', '00:02:10.0000', 606, 110, NULL, NULL, NULL, 8),
(8229, '06/11/2019', '12:17:17.0000', 'US\EASTERN', 'INBOUND', '3473942675', '8008839067', 700, '00:04:24.0000', '00:04:24.0000', 606, 103, NULL, NULL, NULL, 214),
(8231, '06/11/2019', '12:19:00.0000', 'AMERICA\NEW_YORK', 'INBOUND', '3372960285', '8008839067', 700, '00:01:17.0000', '00:01:17.0000',NULL, 700, NULL, NULL, 'Missed', 27),
(8233, '06/11/2019', '12:20:28.0000', 'US\EASTERN', 'INBOUND', '3372960285', '8008839067', 700, '00:01:56.0000', '00:01:55.0000', 606, 110, NULL, NULL, NULL, 66),
(8235, '06/11/2019', '12:20:42.0000', 'US\EASTERN', 'INBOUND', '3303543302', '8008839067', 700, '00:00:51.0000', '00:00:51.0000', 606, 105, NULL, NULL, NULL, 1),
(8237, '06/11/2019', '12:20:45.0000', 'US\EASTERN', 'INBOUND', '3212520428', '8008839067', 700, '00:01:02.0000', '00:01:02.0000', 606, 105, NULL, NULL, NULL, 12)
)
t(VaspianCalls_INDEX, Date, Time, Time_zone, Direction, CallingNumber, DialedNumber, AutoAttendant, Duration, Talk, Target, answer, account, comments, Missed, Hold_Time)
CROSS APPLY (VALUES (dateadd(ss,DATEDIFF(ss,'00:00:00',t.time),convert(datetime,t.date)))) U(StartDateTime)
CROSS APPLY (VALUES (dateadd(ss,50,U.StartDateTime))) W(Answered)
CROSS APPLY (VALUES (dateadd(ss,DATEDIFF(ss,'00:00:00',t.Duration),U.StartDateTime))) X(EndDateTime)
SELECT * FROM #Calls
GO
IF OBJECT_ID('tempdb..#Staff','U') IS NOT NULL DROP TABLE #Staff
GO
SELECT *
INTO #Staff
FROM (VALUES
(CONVERT(int,700), CONVERT(varchar(20),'Miss')),
(110, 'Betty'),
(103, 'Sara'),
(105, 'Jim')) T(StaffId, Name)
GO
SELECT * FROM #StaffGet Results
SELECT s.*,MissedCalls.*
FROM #Calls MissedCalls
CROSS APPLY (VALUES (dateadd(ss,DATEDIFF(ss,'00:00:00',time),convert(datetime,date)))) T(DateTime)
INNER JOIN #Staff s
ON s.StaffId <> 700
WHERE MissedCalls.answer = 700
AND NOT EXISTS(SELECT *
FROM #Calls Unavailable
WHERE Unavailable.answer = s.StaffId
AND Unavailable.StartDateTime < MissedCalls.StartDateTime
AND Unavailable.EndDateTime > MissedCalls.StartDateTime)
<b></b><i></i><u></u>Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply