Count technicians passing on a call

• DixieDean

  Newbie

  Points: 2

  More actions

  June 26, 2013 at 3:15 am

  #275596

  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.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 26, 2013 at 3:37 am

  #1627445

  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

• Mark Cowne

  One Orange Chip

  Points: 26981

  More actions

  June 26, 2013 at 4:06 am

  #1627452

  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

• DixieDean

  Newbie

  Points: 2

  More actions

  June 26, 2013 at 4:23 am

  #1627459

  Brilliant! I had wondered about a self join, but was struggling with how ... Thank you both very much for a lesson learned.