Consolidate Overlapping Datetimes

• NtrsBOB

  SSC Veteran

  Points: 221

  More actions

  July 26, 2017 at 3:00 pm

  #327684

  Scenario: An operator can chat with multiple users throughout a day and I need to calculate how long they actually spent within the chat tool.

  Problem: Each chat interaction is recorded in a table with a start datetime and end datetime. The operator can chat with multiple users concurrently and so there are records with overlapping time periods that cannot be double/triple counted etc.

  Solution: I used a recursive CTE to combine records and recalculate start/end datetimes then I throw out records that still have overlap.
  

  USE master
  GO

  IF OBJECT_ID('tempdb..#DATE_RANGES') IS NOT NULL
      DROP TABLE #DATE_RANGES

  CREATE TABLE #DATE_RANGES
      (
       ID INT IDENTITY(1, 1)
      ,EMPLID INT
      ,StatusType VARCHAR(20)
      ,Created DATETIME
      ,Ended DATETIME
      )

  INSERT  INTO #DATE_RANGES
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-01 12:00' AS Created
                 ,'2017-01-01 14:00' AS Ended
          UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-01 13:00' AS Created
                 ,'2017-01-01 15:00' AS Ended
          UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-01 14:00' AS Created
                 ,'2017-01-01 16:00' AS Ended
          UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-01 17:00' AS Created
                 ,'2017-01-01 18:00' AS Ended
          UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-01 17:00' AS Created
                 ,'2017-01-01 19:00' AS Ended
          UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-01 20:00' AS Created
                 ,'2017-01-01 21:00' AS Ended
          UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-02 13:00' AS Created
                 ,'2017-01-02 14:00' AS Ended
          UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-02 12:00' AS Created
                 ,'2017-01-02 14:00' AS Ended
    UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-03 12:00' AS Created
                 ,'2017-01-03 13:00' AS Ended
    UNION
          SELECT  1 AS EMPLID
                 ,'Active' AS StatusType
                 ,'2017-01-03 11:00' AS Created
                 ,'2017-01-03 14:00' AS Ended

  SELECT  *
  FROM    #DATE_RANGES;

  WITH    CTE_PERIODS
            AS (
                SELECT    EMPLID
                         ,StatusType
                         ,Created
                         ,Ended
                         ,1 AS Iteration
                FROM      #DATE_RANGES
                UNION ALL
                SELECT    #DATE_RANGES.EMPLID
                         ,#DATE_RANGES.StatusType
                         ,CASE WHEN CTE_PERIODS.Created < #DATE_RANGES.Created THEN CTE_PERIODS.Created
                               ELSE #DATE_RANGES.Created
                          END AS Created
                         ,CASE WHEN CTE_PERIODS.Ended > #DATE_RANGES.Ended THEN CTE_PERIODS.Ended
                               ELSE #DATE_RANGES.Ended
                          END AS Ended
                         ,CTE_PERIODS.Iteration + 1 AS Iteration
                FROM      #DATE_RANGES
                          INNER JOIN CTE_PERIODS
                              ON CTE_PERIODS.EMPLID = #DATE_RANGES.EMPLID
                                 AND CTE_PERIODS.StatusType = #DATE_RANGES.StatusType
                                 AND (
                                      CTE_PERIODS.Created BETWEEN #DATE_RANGES.Created AND #DATE_RANGES.Ended --look for records that have overlap
                                      OR CTE_PERIODS.Ended BETWEEN #DATE_RANGES.Created AND #DATE_RANGES.Ended
                                     )
                                 AND (
                                      CTE_PERIODS.Created <> #DATE_RANGES.Created --only join records where an iteration hasn't occurred thus causing the created or end date to be the same as the lookup record's date
                                      AND CTE_PERIODS.Ended <> #DATE_RANGES.Ended
                                     )
               )
      SELECT DISTINCT
              EMPLID
             ,StatusType
             ,Created
             ,Ended
      FROM    CTE_PERIODS AS BASE_CTE
      WHERE   Iteration = (
                           SELECT MAX(MAX_ITERATION.Iteration)
                           FROM   CTE_PERIODS AS MAX_ITERATION
                           WHERE  MAX_ITERATION.EMPLID = BASE_CTE.EMPLID
                                  AND MAX_ITERATION.StatusType = BASE_CTE.StatusType
                                  AND MAX_ITERATION.Created = BASE_CTE.Created
                          )
              AND NOT EXISTS ( SELECT NULL
                               FROM   CTE_PERIODS AS INBETWEEN_PERIOD
                               WHERE  INBETWEEN_PERIOD.Created < BASE_CTE.Created
                                      AND BASE_CTE.Created < INBETWEEN_PERIOD.Ended
                                      OR INBETWEEN_PERIOD.Created < BASE_CTE.Ended
                                      AND BASE_CTE.Ended < INBETWEEN_PERIOD.Ended )

• Jeff Moden

  SSC Guru

  Points: 1004558

  More actions

  July 26, 2017 at 4:50 pm

  #1952926

  See the following article.
  http://sqlmag.com/sql-server/new-solution-packing-intervals-problem

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)