Count technicians passing on a call

  • Here is the table creation script and some sample data (apologies for the varchar date, it's what I have to work with).

    IF OBJECT_ID('tempdb..#updatedb','U') IS NOT NULL

    DROP TABLE #updatedb

    CREATE TABLE #updatedb

    (

    callref INT NOT NULL

    , updatetime VARCHAR(20)

    , repid VARCHAR(44)

    );

    INSERT #updatedb

    SELECT 30572, '25/06/2013 12:58:41', 'JohnSmith'

    UNION

    SELECT 30572, '25/06/2013 13:56:07', 'JoeBloggs'

    UNION

    SELECT 30572, '25/06/2013 13:55:53', 'JoeBloggs'

    UNION

    SELECT 32887, '25/06/2013 15:49:20', 'JohnSmith'

    UNION

    SELECT 32887, '25/06/2013 15:50:10', 'JohnSmith'

    UNION

    SELECT 33610, '25/06/2013 12:23:57', 'JaneJones'

    UNION

    SELECT 34340, '24/06/2013 08:20:09', 'JohnSmith'

    UNION

    SELECT 34340, '24/06/2013 08:20:18', 'JaneJones'

    UNION

    SELECT 34340, '24/06/2013 08:22:24', 'JohnSmith'

    UNION

    SELECT 34340, '24/06/2013 09:24:29', 'JohnSmith'

    UNION

    SELECT 34340, '24/06/2013 10:54:23', 'SueSmith'

    UNION

    SELECT 34340, '25/06/2013 11:44:17', 'JoeBloggs';

    I need to count the total number of times a technician passes a job to someone else. Eg if a technician passes the same job on twice, that counts as 2 and that 2 should be added to any other calls they've passed on. So the expected results from the sample data are JohnSmith 3, SueSmith 1, JaneJones1.

    I thought using

    SET LANGUAGE BRITISH;

    SELECT callref

    , repid

    , rn = ROW_NUMBER() OVER (PARTITION BY callref ORDER BY CAST(updatetime AS SMALLDATETIME) DESC)

    FROM #updatedb;

    would give me a start, but I'm looking at the results and feeling clueless right now. Can someone point me in the right direction please?

    Thank you.

  • That's a good start. Now you need to join that result set to itself on callref = callref and rn = rn + 1, counting the number of times that the two repids are different.

    John

  • As John suggested, note you'll need to use DATETIME rather than SMALLDATETIME

    WITH CTE AS (

    SELECT callref

    , repid

    , updatetime = CAST(updatetime AS DATETIME)

    , rn = ROW_NUMBER() OVER (PARTITION BY callref ORDER BY CAST(updatetime AS DATETIME) )

    FROM #updatedb)

    SELECT a.repid,

    num=COUNT(*)

    FROM CTE a

    WHERE EXISTS (SELECT * FROM CTE b WHERE b.callref = a.callref AND b.rn = a.rn + 1 AND b.repid <> a.repid)

    GROUP BY a.repid

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Brilliant! I had wondered about a self join, but was struggling with how ... Thank you both very much for a lesson learned.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply