CODEWITHSUNDEEP
sqlsql-server-2008
user2989103
user2989103

Reputation: 21

Identify users who only made one call within any 60 minute period

I want to search for calls that been made within an hour from the previous call.group by employees and day.And I only want to show rows with only one call made in the 1 hour interval.

Sample data:

day          calltime                  emplno  empname
2013-11-13   2013-11-13 18:38:39.347   1       James Johnson
2013-11-12   2013-11-12 18:39:57.570   2       Steve Thomson
2013-11-12   2013-11-12 19:05:01.027   2       Steve Thomson
2013-11-12   2013-11-12 15:38:11.853   2       Steve Thomson
2013-11-12  2013-11-12 19:20:01.027    2       Steve Thomson
2013-11-12  2013-11-12 19:45:46.357    2       Steve Thomson
2013-11-12   2013-11-12 18:40:11.853   1       James Johnson

And I want the result like this:

day          calltime                  emplno  empname
2013-11-13   2013-11-13 18:38:39.347   1       James Johnson
2013-11-12  2013-11-12 19:45:46.357    2       Steve Thomson
2013-11-12   2013-11-12 15:38:11.853   2       Steve Thomson
2013-11-12   2013-11-12 18:40:11.853   1       James Johnson

I don't want to show the calls 2013-11-12 18:39:57.570, 2013-11-12 19:05:01.027 and 2013-11-12 19:20:01.027 because the second call happened within an hour of the first call (even though it fell in a different hour).

Upvotes: 2

Views: 88

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

DECLARE @x TABLE([day] DATE, calltime DATETIME, emplno INT, empname VARCHAR(32));

INSERT @x VALUES
('2013-11-13','2013-11-13 18:38:39.347',1,'James Johnson'),
('2013-11-12','2013-11-12 18:39:57.570',2,'Steve Thomson'),
('2013-11-12','2013-11-12 19:05:01.027',2,'Steve Thomson'),
('2013-11-12','2013-11-12 15:38:11.853',2,'Steve Thomson'),
('2013-11-12','2013-11-12 18:40:11.853',1,'James Johnson');


;WITH x AS 
(
  SELECT x.emplno, x.empname, c1 = x.calltime, c2 = x2.calltime 
  FROM @x AS x INNER JOIN @x AS x2 ON x2.emplno = x.emplno 
  AND x2.calltime > x.calltime AND x2.calltime < DATEADD(HOUR, 1, x.calltime)
),
y AS 
(
  SELECT x.emplno, x.empname, x.c1 FROM x
  UNION SELECT x.emplno, x.empname, x.c2 FROM x
)
SELECT [day] = CONVERT(DATE, calltime), calltime, emplno, empname FROM @x
EXCEPT SELECT CONVERT(DATE, c1), c1, emplno, empname FROM y;

Results:

day         calltime                 emplno  empname
----------  -----------------------  ------  -------------
2013-11-12  2013-11-12 15:38:11.853  2       Steve Thomson
2013-11-12  2013-11-12 18:40:11.853  1       James Johnson
2013-11-13  2013-11-13 18:38:39.347  1       James Johnson

Upvotes: 2

D Stanley
D Stanley

Reputation: 152626

One way is to add a Row Number column and do a self-join to compute the difference to the "first" row.

WITH Calls AS
(
    SELECT 
        day, 
        calltime, 
        emplno, 
        empname,
        ROW_NUMBER() OVER (PARTITION BY emplno ORDER BY calltime) RowNum
  FROM CallLog

)
SELECT C1.*
FROM Calls C1
INNER JOIN Calls C2 
  ON C1.EmplNo = c2.EmplNO
     AND C2.RowNum = 1
WHERE DATEDIFF(minute,C2.CallTime, C1.CAllTime) <= 60

Upvotes: 1

Related Questions