SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count technicians passing on a call


Count technicians passing on a call

Author
Message
DixieDean
DixieDean
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
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
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14348 Visits: 15980
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
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3141 Visits: 24128
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
DixieDean
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
Brilliant! I had wondered about a self join, but was struggling with how ... Thank you both very much for a lesson learned.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search