# Determining the Maximum and Average Times between Communications

• ### Question

• Hi All,

Hope you are doing well!...I am trying to find out the maximum and the average timeframe between communications (for communication between the customer and the company -with the customer coming first followed by the response to the customer by the company)...

Suppose if there are multiple responses of the company after a customers questions only the timeframe between the customer question and the 1st company response is to be considered...

In my data set in the action column the communication by the customer is  denoted by cust and the communication by the company is denoted by company and the rowno column arranged in ascending order for every order denotes the flow of conversations...

For example: If an order has multiple customer->company communications

then the maximum time among the multiple conversations and the average time among the multiple conversations is to be taken but the order is always customer followed by company ..the time to be determined is the time taken for the response by a company to the customers question...

create table #orders
(orderid int,
created datetime2,
action varchar(20),
rowno int)

insert into #orders values
('3635763','2019-01-15 23:10:34:321','Cust','1'),
('3635763','2019-01-17 21:14:23:678','Company','2'),
('3635763','2019-01-17 21:20:45:789','Cust','3'),
('3635763','2019-01-20 21:20:45:789','Company','4'),
('3635763','2019-01-23 22:20:45:890','Company','5'),
('3635763','2019-01-25 22:20:34:367','Cust','6'),
('3635763','2019-01-29 22:20:32:567','Company','7'),
('4003456','2019-01-04 06:27:33:567','Cust','1'),
('4003456','2019-01-08 06:27:45:333','Company','2'),
('4003456','2019-01-11 06:27:35:890','Cust','3'),
('4003456','2019-01-15 06:27:33:678','Company','4')

--Output table

create table #output
(orderid int,
maximumtimeforcommunicationinhrs int,

averagetimeforcommunicationinhrs int)

insert into #output values
('3635763','96','71'),
('4003456','96','96')

﻿

Arun

Friday, December 13, 2019 10:29 AM

• ```-- for every "Cust" record , check if the next immediate  record is "Company" & pick   that timeStamp for Hrs Calculation

SELECT OrderID , MAX(Hrs)  AS max_time_for_Communication_hrs , AVG( Hrs)  AS  avg_time_for_Communication_hrs
FROM
(
SELECT [Cust].* , DATEDIFF( MI , [Cust].Created , [Company].Created)/60.00 AS  Hrs FROM #orders AS [Cust]
LEFT JOIN #orders AS [Company]  ON   [Cust].OrderID = [Company].OrderID AND  [Cust].rowNo +1 = [Company].rowNo AND [Company].[action] ='Company'
WHERE [Cust].[action]='Cust'
) b
GROUP BY OrderID```
note : I assume your RowNo is already calculated based on "created" per "OrderID" . or let me know

• Edited by Friday, December 13, 2019 7:39 PM
• Marked as answer by Monday, December 16, 2019 6:24 AM
Friday, December 13, 2019 7:36 PM
• ```SELECT	t.orderid,
MAX(t.timeforcommunicationinhrs) AS maximumtimeforcommunicationinhrs,
AVG(t.timeforcommunicationinhrs) AS averagetimeforcommunicationinhrs
FROM (
SELECT cu.orderid, DATEDIFF(HOUR, cu.created, co.created) AS timeforcommunicationinhrs
FROM (
SELECT *
FROM #orders
WHERE action = 'Cust'
) AS cu
INNER JOIN (
SELECT *, LAG(rowno, 1, 0) OVER(PARTITION BY orderid ORDER BY rowno) AS LeadID
FROM #orders
WHERE action = 'Company'
) AS co ON co.orderid = cu.orderid AND co.rowno > cu.rowno AND co.LeadID < cu.rowno
) AS t
GROUP BY orderid;```

A Fan of SSIS, SSRS and SSAS

• Marked as answer by Monday, December 16, 2019 6:25 AM
Friday, December 13, 2019 9:53 PM
• Hi Arun,

```--create target table first
create table #orders
(orderid int,
created datetime2,
action varchar(20),
rowno int)
--insert data
insert into #orders values
('3635763','2019-01-15 23:10:34:321','Cust','1'),
('3635763','2019-01-17 21:14:23:678','Company','2'),
('3635763','2019-01-17 21:20:45:789','Cust','3'),
('3635763','2019-01-20 21:20:45:789','Company','4'),
('3635763','2019-01-23 22:20:45:890','Company','5'),
('3635763','2019-01-25 22:20:34:367','Cust','6'),
('3635763','2019-01-29 22:20:32:567','Company','7'),
('4003456','2019-01-04 06:27:33:567','Cust','1'),
('4003456','2019-01-08 06:27:45:333','Company','2'),
('4003456','2019-01-11 06:27:35:890','Cust','3'),
('4003456','2019-01-15 06:27:33:678','Company','4')
--output table
SELECT p.orderid,
MAX(p.interval) AS maximumtimeforcommunicationinhrs,
AVG(p.interval) AS averagetimeforcommunicationinhrs
FROM (
SELECT p1.orderid, DATEDIFF(HOUR, p1.created,p2.created) AS interval from #orders p1
JOIN #orders p2
ON p1.orderid=p2.orderid
AND p2.action='company'
AND p2.rowno=p1.rowno+1
)p
GROUP BY orderid
```

Best Regards,

Lily

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Marked as answer by Monday, December 16, 2019 6:26 AM
Monday, December 16, 2019 5:55 AM

### All replies

• ```-- for every "Cust" record , check if the next immediate  record is "Company" & pick   that timeStamp for Hrs Calculation

SELECT OrderID , MAX(Hrs)  AS max_time_for_Communication_hrs , AVG( Hrs)  AS  avg_time_for_Communication_hrs
FROM
(
SELECT [Cust].* , DATEDIFF( MI , [Cust].Created , [Company].Created)/60.00 AS  Hrs FROM #orders AS [Cust]
LEFT JOIN #orders AS [Company]  ON   [Cust].OrderID = [Company].OrderID AND  [Cust].rowNo +1 = [Company].rowNo AND [Company].[action] ='Company'
WHERE [Cust].[action]='Cust'
) b
GROUP BY OrderID```
note : I assume your RowNo is already calculated based on "created" per "OrderID" . or let me know

• Edited by Friday, December 13, 2019 7:39 PM
• Marked as answer by Monday, December 16, 2019 6:24 AM
Friday, December 13, 2019 7:36 PM
• ```SELECT	t.orderid,
MAX(t.timeforcommunicationinhrs) AS maximumtimeforcommunicationinhrs,
AVG(t.timeforcommunicationinhrs) AS averagetimeforcommunicationinhrs
FROM (
SELECT cu.orderid, DATEDIFF(HOUR, cu.created, co.created) AS timeforcommunicationinhrs
FROM (
SELECT *
FROM #orders
WHERE action = 'Cust'
) AS cu
INNER JOIN (
SELECT *, LAG(rowno, 1, 0) OVER(PARTITION BY orderid ORDER BY rowno) AS LeadID
FROM #orders
WHERE action = 'Company'
) AS co ON co.orderid = cu.orderid AND co.rowno > cu.rowno AND co.LeadID < cu.rowno
) AS t
GROUP BY orderid;```

A Fan of SSIS, SSRS and SSAS

• Marked as answer by Monday, December 16, 2019 6:25 AM
Friday, December 13, 2019 9:53 PM
• Hi Arun,

```--create target table first
create table #orders
(orderid int,
created datetime2,
action varchar(20),
rowno int)
--insert data
insert into #orders values
('3635763','2019-01-15 23:10:34:321','Cust','1'),
('3635763','2019-01-17 21:14:23:678','Company','2'),
('3635763','2019-01-17 21:20:45:789','Cust','3'),
('3635763','2019-01-20 21:20:45:789','Company','4'),
('3635763','2019-01-23 22:20:45:890','Company','5'),
('3635763','2019-01-25 22:20:34:367','Cust','6'),
('3635763','2019-01-29 22:20:32:567','Company','7'),
('4003456','2019-01-04 06:27:33:567','Cust','1'),
('4003456','2019-01-08 06:27:45:333','Company','2'),
('4003456','2019-01-11 06:27:35:890','Cust','3'),
('4003456','2019-01-15 06:27:33:678','Company','4')
--output table
SELECT p.orderid,
MAX(p.interval) AS maximumtimeforcommunicationinhrs,
AVG(p.interval) AS averagetimeforcommunicationinhrs
FROM (
SELECT p1.orderid, DATEDIFF(HOUR, p1.created,p2.created) AS interval from #orders p1
JOIN #orders p2
ON p1.orderid=p2.orderid
AND p2.action='company'
AND p2.rowno=p1.rowno+1
)p
GROUP BY orderid
```

Best Regards,

Lily

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Marked as answer by Monday, December 16, 2019 6:26 AM
Monday, December 16, 2019 5:55 AM
• @msbi_Dev :Really appreciate your help!...

Yes the Rowno is already calculated based on created per orderid...

Thanks,

Arun

Arun

Monday, December 16, 2019 6:25 AM
• Thanks Guoxiong!...Really appreciate your help!..

Thanks,

Arun

Arun

Monday, December 16, 2019 6:26 AM
• Lily Lii:     Thanks !..Really appreciate your response!..
 90 Points Top 15
 Lily Lii Joined Nov 2019 7

Arun

Monday, December 16, 2019 6:27 AM