Consider a customer care center which receives calls from customers, Starttime and Endtime denotes the conversation start time and end time. Missed calls are the calls where no conversation happened. For any missed call, the customer care returns the call.
For below given table,
CREATE TABLE CustomerCare (fromnumber INT, tonumber INT, starttime DATETIME,endtime DATETIME)
INSERT INTO CustomerCare (fromnumber,tonumber,starttime,endtime)
VALUES
(100,1800,'2019-08-13 18:40:00','2019-08-13 18:40:00'),
(1800,100,'2019-08-13 18:55:00','2019-08-13 18:57:00'),
(200,1800,'2019-08-13 19:30:00','2019-08-13 19:30:00'),
(1800,200,'2019-08-13 20:05:00','2019-08-13 20:10:00'),
(300,1800,'2019-08-13 21:00:00','2019-08-13 21:00:00'),
(1800,300,'2019-08-13 21:20:00','2019-08-13 21:25:00'),
(400,1800,'2019-08-13 07:00:00','2019-08-13 07:00:00'),
(500,1800,'2019-08-13 8:00:00','2019-08-13 8:05:00')
Find the number of return calls that were done by customer care within 30 minutes of the missed call.
Fourth row indicates one such return call.
Can anyone help a SQL query.
3条答案
按热度按时间wz1wpwve1#
first you find the missed call, then you find the call back by customercare. And you find the time different
utugiqy62#
@Squirrel beat me to it and my answer original answer was basically identical to his but I'll post what I put together as it also speaks to why APPLY is so excellent.
First, for optimal performance you would want a clustered index (or a covering index) on starttime. E.g.
Next, here's how you would do this using a JOIN (since you mentioned it):
Now, the APPLY version (this is what I put together before I saw Squirrels answer):
Both Return:
The first cool thing about APPLY is how I can use it to alias values to make my code "DRYer." (DRY = Don't Repeat Yourself). Since the expression
datediff(minute, cc.starttime, x.starttime)
is used multiple times, I can use APPLY to process the expression once then reference it multiple times as cb.t. Note the line:This is an example of how APPLY can make your code much cleaner and easier to debug.
The second and possibly more important example of how the powerful of APPLY is how you can join an outer query with a subquery. My two examples above are not Identical. Theoretically, if there were more than one "call back" within 30 minutes, the JOIN version would return them. Since this is likely an edge case, asking for the "TOP (1)" returned call within a half hour will be sufficient and will perform better. If you examine the execution plan for both queries - the join version reads more rows (28) when it self joints to CustomerCare, the APPLY version reads 22 rows. If you change TOP (1) to TOP ([anything greater than 1]) in the APPLY version it will read 28 rows. Again, this is another way you can use APPLY to tune performance.
Lastly, an important note about TOP in a subquery... If you remove the ORDER BY in the APPLY subquery the subquery will read 58 rows instead of 22 when an ORDER BY over a correctly indexed column is present.
djp7away3#
here A is the table which holds missed calls…