Gets the time for a request to reach a certain status in HelpSpot

timeToStatus.sql

  SELECT HS_Request.xRequest, 
       Cast(Floor(Round((ClosedEvents.ClosedTime-dtGMTOpened) / 60.0, 0) * 60 / 
       86400) 
       AS VARCHAR(10)) 
       + 'd ' 
       + CONVERT(VARCHAR(5), Dateadd(second, 
       Round((ClosedEvents.ClosedTime-dtGMTOpened) / 60.0, 0) * 60, '19000101'), 
       8) AS 
       opentime 
FROM   HS_Request 
       JOIN (SELECT [xRequest], 
                    Min([dtLogged]) AS ClosedTime 
             FROM   [HS_Request_Events] 
             WHERE  sColumn = 'xStatus' 
                    AND sLabel = 'Problem Solved' 
             GROUP  BY xRequest) AS ClosedEvents 
         ON HS_Request.xRequest = ClosedEvents.xRequest;