Count technicians passing on a call

Question

Count technicians passing on a call

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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: 26986 More actions · Answer 2

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 · Answer 3

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