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

    Question

  • 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.Loop in one hour intervals, if there is more than one call in one hour, delete them and start the second loop from the next call

    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). Is it possible to build a query like this?


    • Edited by andy910 Friday, November 15, 2013 10:21 PM
    Friday, November 15, 2013 10:20 PM

Answers

  • When asking a question, you should tell us the release level of SQL you are using.  The best answer may depend on what level you are running.

    Declare @Test Table(day date,         calltime datetime,                emplno int, empname varchar(20));
    Insert @Test(day, calltime, emplno, empname) 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 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');
    
    -- SQL 2005, 2008, and 2008R2
    ;With cte As
    (Select day, calltime, emplno, empname,
      Row_Number() Over(Partition By emplno Order By calltime) As rn
    From @Test)
    Select c1.day, c1.calltime, c1.emplno, c1.empname
    From cte c1
    Left Join cte c2 On c1.emplno = c2.emplno And c1.rn = c2.rn - 1
    Where DateDiff(second, c1.calltime, c2.calltime) > 3600 Or c2.calltime Is Null;
    
    -- SQL 2012 and later
    ;With cte As
    (Select day, calltime, emplno, empname,
      Lead(calltime) Over(Partition By emplno Order By calltime) As LastCallTime
    From @Test)
    Select day, calltime, emplno, empname
    From cte
    Where DateDiff(second, calltime, LastCallTime) > 3600 Or LastCallTime Is Null;

    Tom

    Saturday, November 16, 2013 12:06 AM
  • >> I want to search for calls that been made within an hour from the previous call, group by employees and day. <<

    Why did you fail to post DDL? Why did you fail to normalize what little you did post? Personnel are entities not attributes so they get a table that is referenced. 

    CREATE TABLE Personnel
    (emp_nbr INTEGER NOT NULL PRIMARY KEY, 
     emp_name VARCHAR(35) NOT NULL);

    INSERT INTO Personnel
    VALUES 
    (1, 'James Johnson'), 
    (2, 'Steve Thomson');

    We have DATE, TIME and DATETIME2(n) data type now, so there is no need for the old Sybase DATETIME. DAY is both a reserved word and redundant, since can get the day from the call_timestamp. 

    You also failed to post a key, so your ASCII picture is not a table by definition! My guess is the timestamp, but who knows??

    CREATE TABLE Phonecall_Log
    (call_timestamp DATETIME2(0) 
      DEFAULT CURRENT_TIMESTAMP NOT NULL,
     emp_nbr INTEGER NOT NULL 
     REFERENCES Personnel(emp_id), 

    INSERT INTO Phonecall_Log
    VALUES
    ('2013-11-13 18:38:39', 1), 
    ('2013-11-12 18:39:57', 2), 
    ('2013-11-12 19:05:01', 2), 
    ('2013-11-12 15:38:11', 2), 
    ('2013-11-12 19:20:01', 2), 
    ('2013-11-12 19:45:46', 2), 
    ('2013-11-12 18:40:11', 1);

    What you are trying to do is called a delta query.

    SELECT emp_nbr, call_timestamp,
           DATEDIFF (MI, call_timestamp, LAG(call_timestamp) 
           OVER (PARTITION BY emp_nbr ORDER BY call_timestamp)
           AS call_delta
      FROM Phonecall_Log;

    Here are the results:

    1 2013-11-12 18:40:11 NULL
    1 2013-11-13 18:38:39 -1438
    2 2013-11-12 15:38:11 NULL
    2 2013-11-12 18:39:57 -181
    2 2013-11-12 19:05:01 -26
    2 2013-11-12 19:20:01 -15
    2 2013-11-12 19:45:46 -25

    The delta is shown as elapsed minutes between calls for each call within each employee. The NULL is the starting call in the employee’s series. Put this in a VIEW, so you always have current data.  

    >> And I only want to show rows with only one call made in the 1 hour interval. Loop in one hour intervals, if there is more than one call in one hour, delete them and start the second loop from the next call <<

    We do not loop in SQL; it is a declarative language. INTERVAL is a reserved word in Standard SQL. If you want to have timeslots, then you need a table for them. 

     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, November 16, 2013 12:30 AM

All replies

  • When asking a question, you should tell us the release level of SQL you are using.  The best answer may depend on what level you are running.

    Declare @Test Table(day date,         calltime datetime,                emplno int, empname varchar(20));
    Insert @Test(day, calltime, emplno, empname) 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 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');
    
    -- SQL 2005, 2008, and 2008R2
    ;With cte As
    (Select day, calltime, emplno, empname,
      Row_Number() Over(Partition By emplno Order By calltime) As rn
    From @Test)
    Select c1.day, c1.calltime, c1.emplno, c1.empname
    From cte c1
    Left Join cte c2 On c1.emplno = c2.emplno And c1.rn = c2.rn - 1
    Where DateDiff(second, c1.calltime, c2.calltime) > 3600 Or c2.calltime Is Null;
    
    -- SQL 2012 and later
    ;With cte As
    (Select day, calltime, emplno, empname,
      Lead(calltime) Over(Partition By emplno Order By calltime) As LastCallTime
    From @Test)
    Select day, calltime, emplno, empname
    From cte
    Where DateDiff(second, calltime, LastCallTime) > 3600 Or LastCallTime Is Null;

    Tom

    Saturday, November 16, 2013 12:06 AM
  • >> I want to search for calls that been made within an hour from the previous call, group by employees and day. <<

    Why did you fail to post DDL? Why did you fail to normalize what little you did post? Personnel are entities not attributes so they get a table that is referenced. 

    CREATE TABLE Personnel
    (emp_nbr INTEGER NOT NULL PRIMARY KEY, 
     emp_name VARCHAR(35) NOT NULL);

    INSERT INTO Personnel
    VALUES 
    (1, 'James Johnson'), 
    (2, 'Steve Thomson');

    We have DATE, TIME and DATETIME2(n) data type now, so there is no need for the old Sybase DATETIME. DAY is both a reserved word and redundant, since can get the day from the call_timestamp. 

    You also failed to post a key, so your ASCII picture is not a table by definition! My guess is the timestamp, but who knows??

    CREATE TABLE Phonecall_Log
    (call_timestamp DATETIME2(0) 
      DEFAULT CURRENT_TIMESTAMP NOT NULL,
     emp_nbr INTEGER NOT NULL 
     REFERENCES Personnel(emp_id), 

    INSERT INTO Phonecall_Log
    VALUES
    ('2013-11-13 18:38:39', 1), 
    ('2013-11-12 18:39:57', 2), 
    ('2013-11-12 19:05:01', 2), 
    ('2013-11-12 15:38:11', 2), 
    ('2013-11-12 19:20:01', 2), 
    ('2013-11-12 19:45:46', 2), 
    ('2013-11-12 18:40:11', 1);

    What you are trying to do is called a delta query.

    SELECT emp_nbr, call_timestamp,
           DATEDIFF (MI, call_timestamp, LAG(call_timestamp) 
           OVER (PARTITION BY emp_nbr ORDER BY call_timestamp)
           AS call_delta
      FROM Phonecall_Log;

    Here are the results:

    1 2013-11-12 18:40:11 NULL
    1 2013-11-13 18:38:39 -1438
    2 2013-11-12 15:38:11 NULL
    2 2013-11-12 18:39:57 -181
    2 2013-11-12 19:05:01 -26
    2 2013-11-12 19:20:01 -15
    2 2013-11-12 19:45:46 -25

    The delta is shown as elapsed minutes between calls for each call within each employee. The NULL is the starting call in the employee’s series. Put this in a VIEW, so you always have current data.  

    >> And I only want to show rows with only one call made in the 1 hour interval. Loop in one hour intervals, if there is more than one call in one hour, delete them and start the second loop from the next call <<

    We do not loop in SQL; it is a declarative language. INTERVAL is a reserved word in Standard SQL. If you want to have timeslots, then you need a table for them. 

     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, November 16, 2013 12:30 AM
  • It is a good idea to provide the SQL Server version and the SQL scripts to generate test data. You table is also not normalized so please look at Celko's post for a normalized structure.

    -- setup test data
    
    declare @t table (id tinyint identity, callday date, calltime datetime, emplno tinyint, empname varchar(20))
    
    insert into @t (callday, calltime, emplno, empname) 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 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')
    
    ---------------------------------------
    -- The query with your expected results
    ;with 
    cte1 as ( -- get row number
    select *, row_number() over(partition by emplno order by calltime) as rownum from @t
    ),
    cte2 as ( -- get difference in minutes
    select *, datediff(mi, calltime, (select c2.calltime 
    									from cte1 as c2 
    									where c2.emplno = c1.emplno 
    									and c2.rownum = c1.rownum-1)
    								  ) as MinutesSinceLastCall
    from cte1 as c1
    )
    ,
    cte3 as (-- get rows with difference more than 60 mins
    select * from cte2
    where MinutesSinceLastCall <= -60
    )
    -- final query using ctes to get the rows
    select cte1.* from cte1 
    inner join cte3 on cte1.emplno= cte3.emplno and cte1.rownum = cte3.rownum
    union
    select cte1.* from cte1 
    inner join cte3 on cte1.emplno= cte3.emplno and cte1.rownum = cte3.rownum-1
    order by cte1.emplno, cte1.rownum


    - Aalam | (Blog)

    Saturday, November 16, 2013 1:55 AM
  • 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). Is it possible to build a query like this?

    I'm not sure why have you included this row in your expected result set -

    2013-11-12  2013-11-12 19:45:46.357    2       Steve Thomson

    Isn't it just 20 mins off from -

    2013-11-12  2013-11-12 19:20:01.027    2       Steve Thomson


    - Aalam | (Blog)

    Saturday, November 16, 2013 2:16 AM