June 26, 2013 at 3:15 am
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.
June 26, 2013 at 3:37 am
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
June 26, 2013 at 4:06 am
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/61537June 26, 2013 at 4:23 am
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