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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy