July 26, 2017 at 3:00 pm
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 )
July 26, 2017 at 4:50 pm
See the following article.
http://sqlmag.com/sql-server/new-solution-packing-intervals-problem
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply