Best/Fastest way to cursor(?) through data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best/Fastest way to cursor(?) through data

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-09-13 : 04:03:21

Hi all

I have the following recordset:-


CallId       TypeId	EventModifiedAt 	User	       UserId
41512428	11	08/09/2012 00:00:44	Health Advisor	8845
41512428	36	08/09/2012 00:08:08	Health Advisor	8845
41512429	11	08/09/2012 00:02:55	Health Advisor	10853
41512429	23	08/09/2012 00:20:42	Health Advisor	10853
41512429	24	08/09/2012 00:25:18	Nurse Advisor	12508
41512429	23	08/09/2012 00:26:10	Nurse Advisor	12508
41512429	24	08/09/2012 00:28:51	Nurse Advisor	11700
41512429	36	08/09/2012 00:42:41	Nurse Advisor	11700
41512430	11	08/09/2012 00:04:09	Health Advisor	11471
41512430	36	08/09/2012 00:17:57	Health Advisor	11471
41512458	11	08/09/2012 00:01:24	Health Advisor	1568
41512458	23	08/09/2012 00:08:01	Health Advisor	1568
41512458	24	08/09/2012 00:18:46	Nurse Advisor	11087
41512458	36	08/09/2012 00:29:01	Nurse Advisor	11087
41512459	11	08/09/2012 00:03:10	Health Advisor	11236
41512459	36	08/09/2012 00:10:24	Health Advisor	11236
41512460	11	08/09/2012 00:03:41	Health Advisor	11826
41512460	23	08/09/2012 00:11:15	Health Advisor	11826
41512460	24	08/09/2012 00:15:12	Health Advisor	5126
41512460	23	08/09/2012 00:24:56	Health Advisor	5126
41512460	24	08/09/2012 00:25:52	Health Advisor	5126
41512460	23	08/09/2012 00:26:03	Health Advisor	5126
41512460	24	08/09/2012 00:26:06	Nurse Advisor	10597
41512460	36	08/09/2012 00:43:02	Nurse Advisor	10597

and I need to get the time difference for each call and usertype.
However, if the same user does multiple things to the call, I don't want the breaks between each section.
For example, on Call Id 41512429, the health advisor is quite easy as I can just take the time difference between the two TypeIDs.
Unfortunately for the Nurse Advisor bit, I need to take the time difference between TypeIDs 24 and 23 for user 12508 and add that to the time difference between TypeIDs 24 and 36 for user 11700.

I have tried using MIN and MAX but that includes the gap which I don't want as the call was "idle" for a short period and I want to ignore that.

So, for that call, I'd want 1067 seconds for the Health Advisor section and 881 seconds for the Nurse Advisor section.

I've been wracking my brains (such as they are) for the last couple of days over this so any help gratefully received.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-13 : 11:54:16

Given your sample data set, what would you want for output? Also, any chance you can put your data in a consumable format (so we don't have to type it in)?

These links might help with that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-13 : 13:25:57

Does this work?

-- setup
CREATE TABLE #calls (CallId BIGINT NOT NULL, TypeId TINYINT NOT NULL, EventModifiedAt DATETIME NOT NULL, UserName VARCHAR(25) NOT NULL, UserId INT NOT NULL)
INSERT #calls
SELECT 41512428,11,'08/09/2012 00:00:44','Health Advisor',8845 UNION ALL 
SELECT 41512428,36,'08/09/2012 00:08:08','Health Advisor',8845 UNION ALL 
SELECT 41512429,11,'08/09/2012 00:02:55','Health Advisor',10853 UNION ALL 
SELECT 41512429,23,'08/09/2012 00:20:42','Health Advisor',10853 UNION ALL 
SELECT 41512429,24,'08/09/2012 00:25:18','Nurse Advisor',12508 UNION ALL 
SELECT 41512429,23,'08/09/2012 00:26:10','Nurse Advisor',12508 UNION ALL 
SELECT 41512429,24,'08/09/2012 00:28:51','Nurse Advisor',11700 UNION ALL 
SELECT 41512429,36,'08/09/2012 00:42:41','Nurse Advisor',11700 UNION ALL 
SELECT 41512430,11,'08/09/2012 00:04:09','Health Advisor',11471 UNION ALL 
SELECT 41512430,36,'08/09/2012 00:17:57','Health Advisor',11471 UNION ALL 
SELECT 41512458,11,'08/09/2012 00:01:24','Health Advisor',1568 UNION ALL 
SELECT 41512458,23,'08/09/2012 00:08:01','Health Advisor',1568 UNION ALL 
SELECT 41512458,24,'08/09/2012 00:18:46','Nurse Advisor',11087 UNION ALL 
SELECT 41512458,36,'08/09/2012 00:29:01','Nurse Advisor',11087 UNION ALL 
SELECT 41512459,11,'08/09/2012 00:03:10','Health Advisor',11236 UNION ALL 
SELECT 41512459,36,'08/09/2012 00:10:24','Health Advisor',11236 UNION ALL 
SELECT 41512460,11,'08/09/2012 00:03:41','Health Advisor',11826 UNION ALL 
SELECT 41512460,23,'08/09/2012 00:11:15','Health Advisor',11826 UNION ALL 
SELECT 41512460,24,'08/09/2012 00:15:12','Health Advisor',5126 UNION ALL 
SELECT 41512460,23,'08/09/2012 00:24:56','Health Advisor',5126 UNION ALL 
SELECT 41512460,24,'08/09/2012 00:25:52','Health Advisor',5126 UNION ALL 
SELECT 41512460,23,'08/09/2012 00:26:03','Health Advisor',5126 UNION ALL 
SELECT 41512460,24,'08/09/2012 00:26:06','Nurse Advisor',10597 UNION ALL 
SELECT 41512460,36,'08/09/2012 00:43:02','Nurse Advisor',10597

--query
;WITH CTE AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY CallId, UserID ORDER BY EventModifiedAt) rownum FROM #calls)
, CTE2 AS (SELECT a.*, ISNULL(DATEDIFF(SECOND,b.EventModifiedAt,a.EventModifiedAt),0) duration 
FROM CTE a LEFT JOIN CTE b ON a.CallId=b.CallId AND a.UserId=b.UserId AND a.rownum=b.rownum+1)
SELECT CallID, UserName, SUM(Duration) TotalDuration
FROM CTE2
GROUP BY CallID, UserName
ORDER BY CallID, UserName

You don't need the first (setup) part if you already have data, you just need to change references to #calls in the 2nd part to match your table name.

Generally speaking, "best" and "fastest" don't belong in the same sentence as "cursor".

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-09-14 : 02:02:13

Robvolk - I've changed the query to this:-


;WITH CTE AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY CallId, UserID ORDER BY EventModifiedAt) rownum FROM #front_ending_input)
, CTE2 AS (SELECT a.*, ISNULL(DATEDIFF(SECOND,b.EventModifiedAt,a.EventModifiedAt),0) duration 
FROM CTE a LEFT JOIN CTE b ON a.CallId=b.CallId AND a.UserId=b.UserId AND a.rownum=b.rownum+1)
SELECT CallID, UserName, SUM(Duration) TotalDuration
FROM CTE2
GROUP BY CallID, UserName
ORDER BY CallID, UserName

but I'm getting errors on the highlighted bits.

Lamprey - I'll see what I can do about the formatting, etc but the SELECT/INSERT from robvolk should work for you.

As for the outputs, all I want is the following:-
For call 41512428, I'd want the ID number, the Usertype (Health Advisor) and the difference in time between code 11 and code 36 (444 seconds).
For call 41512429, I'd want two records as follows:-
ID number, Health Advisor and the difference in time between codes 11 and 23 (1067 seconds)
and then
ID Number, Nurse Advisor and the difference between codes 24 and 23 (52 seconds) plus the difference in time between the second code 24 and code 36 (830 seconds) to give me a total of 882 seconds.

So as an output, I'd want


Call ID     Usertype          AHT
41512428    Health Advisor    444
41512429    Health Advisor    1067
41512429    Nurse Advisor     882

The only oddity in this is if you find a Nurse Advisor with a code of 11, the this needs to be logged as "Nurse Advisor Front Ending" but the same rules apply.

Hopefully that makes sense.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-14 : 09:42:34

What errors are you getting? Are the column names in #front_ending_input different?

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-09-17 : 02:21:37

I've sorted the erros out (it was referencing a field, username, I'd removed as unecessary. Username and userid are interchangeable for this.) and the query has now become this:-


;WITH CTE AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY CallId, UserID ORDER BY calleventmodifiedat) rownum FROM #front_ending_input)
, CTE2 AS (SELECT a.*, ISNULL(DATEDIFF(SECOND,b.calleventmodifiedat,a.calleventmodifiedat),0) duration 
FROM CTE a LEFT JOIN CTE b ON a.CallId=b.CallId AND a.UserId=b.UserId AND a.rownum=b.rownum+1)
SELECT CallID, userid, SUM(Duration) TotalDuration
FROM CTE2
GROUP BY CallID, userid
ORDER BY CallID, userid

Unfortunately, it's bringing back no records whatsoever.

::edit::

Ignore that, I'd dropped the code in the wrong place.
I'll try again now it's in the right place.

::edit::

That now works fine, except I need to include the first eventtypeid (11,24 or 37) and the userclassification of each user as well.
Both of these are used further down in the code to find out which field the AHT goes into.

I've tried to alter the code to include the eventtypeid but got duplicate rows and one of the AHTs is zero.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-17 : 06:39:34

You have to post sample data and the exact output you need. Please include the full DDL for the tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources