none
Determining the Maximum and Average Times between Communications RRS feed

  • 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...

    Please find below the DDL and the expected output with the screenshot of working calculations...Can you please help here..

    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

Answers

  • -- 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 msbi_Dev Friday, December 13, 2019 7:39 PM
    • Marked as answer by Hellothere8028 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 Hellothere8028 Monday, December 16, 2019 6:25 AM
    Friday, December 13, 2019 9:53 PM
  • Hi Arun,

    Please try:

    --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 Hellothere8028 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 msbi_Dev Friday, December 13, 2019 7:39 PM
    • Marked as answer by Hellothere8028 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 Hellothere8028 Monday, December 16, 2019 6:25 AM
    Friday, December 13, 2019 9:53 PM
  • Hi Arun,

    Please try:

    --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 Hellothere8028 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