Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count technicians passing on a call Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 8:39 AM
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.
Post #1467526
Posted Wednesday, June 26, 2013 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,421, Visits: 10,077
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
Post #1467535
Posted Wednesday, June 26, 2013 4:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:38 AM
Points: 1,678, Visits: 19,553
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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1467543
Posted Wednesday, June 26, 2013 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 8:39 AM
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.
Post #1467551
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse