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
 Cursor issues

Author  Topic 

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 02:27:43
Hi all

Before anyone says it, I know cursors are usually bad news but I can't see any way around it in this instance.

Anyway, I've got a cursor which which should go through a dataset and pick up certain items.
I've got the base data below:-

CallId CallEventID UserId CallEventModifiedAt
43494439 1 10853 23/01/2013 00:00:21
43494439 2 10853 23/01/2013 00:00:21
43494439 7 10853 23/01/2013 00:03:31
43494439 11 10853 23/01/2013 00:04:35
43494439 12 10853 23/01/2013 00:05:17
43494439 13 10853 23/01/2013 00:05:17
43494439 14 4070 23/01/2013 00:06:25
43494439 15 4070 23/01/2013 00:06:25
43494439 20 4070 23/01/2013 00:19:44
43494439 21 4070 23/01/2013 00:19:44
43494439 24 4070 23/01/2013 00:21:20
43494439 29 4 23/01/2013 00:23:03
43494439 30 4070 23/01/2013 00:23:30
43494439 31 4070 23/01/2013 00:23:30
43494439 32 4 23/01/2013 00:23:48


This is my output:-

CallId UserId Start_Time Stop_Time
43494439 10853 23/01/2013 00:00:21 23/01/2013 00:05:17
43494439 4070 23/01/2013 00:06:25 23/01/2013 00:23:30
43494439 4 23/01/2013 00:23:03 23/01/2013 00:23:48
43494439 4070 23/01/2013 00:23:30 23/01/2013 00:23:30
43494439 4 23/01/2013 00:23:48 23/01/2013 00:23:48


What should happen is that each start/stop time should be based on the change of UserID

So, for the first time UserID 4070 appears, the Stop_Time should be 23/01/2013 00:21:20 as that's where their "chunk" ends and a new user takes over.

Everything else seems to pick up OK.

Here's the code for the cursor:-

declare @callid_old varchar(15)
declare @userid_old int
declare @Start_time_old datetime

declare @callid varchar(15)
declare @userid int
declare @Start_time datetime

declare loop_cursor cursor for
select
callid
,userid
,calleventmodifiedat
from
#original_crystal
order by
callid
,calleventmodifiedat

open loop_cursor
fetch next from loop_cursor
into @callid, @userid, @start_time

insert into #temp_times
(callid
,UserId
,Start_Time
)
values
(@callid
,@userid
,@Start_time
)

while @@FETCH_STATUS=0
begin
if @callid<>@callid_old
begin
update #temp_times
set stop_time=@Start_time_old
where
callid=@callid_old
and UserId=@userid_old

insert into #temp_times
(callid
,UserId
,Start_Time
)
values
(@callid
,@userid
,@Start_time
)
end
else
if @userid<>@userid_old
begin
insert into #temp_times
(callid
,UserId
,Start_Time
)
values
(@callid
,@userid
,@Start_time
)

update #temp_times
set stop_time=@Start_time_old
where
callid=@callid
and UserId=@userid_old
end

set @callid_old = @callid
set @userid_old = @userid
set @Start_time_old = @Start_time

fetch next from loop_cursor
into @callid, @userid, @start_time

end

update #temp_times
set stop_time=@Start_time_old
where
callid=@callid
and UserId=@userid_old

close loop_cursor
deallocate loop_cursor


As far as I can see, the cursor is written correctly but I can't understand why it's not picking up the first end time for this particular user.

Anyone any ideas/suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 03:20:54
you dont need a cursor for this.
see solution below

SELECT CallId,UserId,MIN(CallEventModifiedAt) AS Start_Time,MAX(CallEventModifiedAt) AS End_Time
FROM #original_crystal t
OUTER APPLY (SELECT MIN(CallEventModifiedAt) AS MinDate
FROM #original_crystal
WHERE CallEventModifiedAt> t.CallEventModifiedAt
AND UserId <> t.UserId
)t1
GROUP BY CallId,UserId,MinDate


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 03:42:40
Thanks for that.
Can you explain how it works please, it's the first I've come across this type of solution?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 03:58:29
quote:
Originally posted by rmg1

Thanks for that.
Can you explain how it works please, it's the first I've come across this type of solution?


OUTER APPLY will run a correlated query for each record in your table which retrieves the earliest date for same callid where user changed. then grouping on this field will give you data grouped based on each "chunk". Then apply MIN and MAX over dates to get starting and ending date in each group.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 04:32:56
Thanks for the explanation, I think I understand it now.

I've just tried the code and it seems to take far longer than the cursor.

Any ideas why?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 04:33:53
hmm..what are indexes you've on #original_crystal? how big is the dataset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 05:21:55
There's no indexes on the temp table at all, didn't think they were needed.
The dataset is around 500000 rows.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 05:27:28
check execution plan and see what are costly steps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 05:34:05
I've got the estimated execution plan and the step with the greatest percentage is marked:-
Index Spool (Eager Spool) 69%

Not sure what else is needed.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 06:09:09
try this too

http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 06:30:48
Thanks for that.
How would I create an index on the temp table (and would I need to drop it as part of my general "drop temp tables" section before dropping the table)?

I'm not sure which columns to index and which type of index to use but I'm assuming an index on my #original_crystal table would help matters.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 07:52:17
If you are using the query Visakh posted, try an index on CallEventModifiedAt and UserId (or UserId included), for example:
CREATE INDEX AnIndexName ON #original_crystal
(CallEventModifiedAt) INCLUDE (UserId)
You don't need to explicitly drop the index on the temp tables; dropping the temp table will cause the index to be automatically dropped.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 08:29:30
That's speeded things up, but unfortunately that piece of script doesn't work.
This is some new base data:-
CallId	CallEventID	StartTime	UserId	CallEventTypeId	CallEventModifiedAt
43505928 1 24/01/2013 00:01:23 11776 11 24/01/2013 00:01:23
43505928 2 24/01/2013 00:01:23 11776 5 24/01/2013 00:01:23
43505928 7 24/01/2013 00:01:23 11776 25 24/01/2013 00:05:07
43505928 11 24/01/2013 00:01:23 11776 50 24/01/2013 00:06:53
43505928 12 24/01/2013 00:01:23 11776 50 24/01/2013 00:07:11
43505928 13 24/01/2013 00:01:23 11776 6 24/01/2013 00:07:45
43505928 14 24/01/2013 00:01:23 11776 23 24/01/2013 00:07:45
43505928 15 24/01/2013 00:01:23 6737 24 24/01/2013 00:07:56
43505928 16 24/01/2013 00:01:23 6737 5 24/01/2013 00:07:56
43505928 19 24/01/2013 00:01:23 6737 26 24/01/2013 00:18:53
43505928 20 24/01/2013 00:01:23 6737 27 24/01/2013 00:18:53
43505928 23 24/01/2013 00:01:23 6737 6 24/01/2013 00:19:02
43505928 24 24/01/2013 00:01:23 6737 36 24/01/2013 00:19:02


And this is the output:-
CallId	UserId	Start_Time	Stop_Time
43505928 11776 24/01/2013 00:01:23 24/01/2013 00:01:23
43505928 11776 24/01/2013 00:05:07 24/01/2013 00:05:07
43505928 11776 24/01/2013 00:06:53 24/01/2013 00:06:53
43505928 11776 24/01/2013 00:07:11 24/01/2013 00:07:11
43505928 11776 24/01/2013 00:07:45 24/01/2013 00:07:45
43505928 6737 24/01/2013 00:07:56 24/01/2013 00:07:56
43505928 6737 24/01/2013 00:18:53 24/01/2013 00:18:53
43505928 6737 24/01/2013 00:19:02 24/01/2013 00:19:02


What I should have is:-
UserID 11776 - Start Time 24/01/2013 00:01:23
UserID 11776 - Stop Time 24/01/2013 00:07:45
UserID 6737 - Start Time 24/01/2013 00:07:56
UserID 6737 - Stop Time 24/01/2013 00:19:02
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 08:46:01
Can you try this?
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CallId ORDER BY CallEventModifiedAt)
- ROW_NUMBER() OVER (PARTITION BY CallId,UserId ORDER BY CallEventModifiedAt) AS GroupID
FROM
TheTable
)
SELECT CallId,
UserId,
MIN(CallEventModifiedAt) AS StartTime,
MAX(CallEventModifiedAt) AS EndTime
FROM CTE
GROUP BY
CallId,
UserId,
GroupId;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 09:41:32
quote:
Originally posted by rmg1

That's speeded things up, but unfortunately that piece of script doesn't work.




which piece of script?
what was the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-25 : 09:43:55
James K - Thank you! Looks like that's got it cracked.

Visakh16 - There was no error, it was just grouping incorrectly by the Event ID when it didn't need to.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 10:47:21
You are very welcome - glad to be of help.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-28 : 04:44:03
Hi all

Unfortunately, what I thought was working hasn't.
This is an adjusted CTE (original code from James K):

;WITH cte AS
(
SELECT
CallId
,userid
,calleventmodifiedat
,ROW_NUMBER() OVER (PARTITION BY CallId ORDER BY CallEventModifiedAt)
- ROW_NUMBER() OVER (PARTITION BY CallId,UserId ORDER BY CallEventModifiedAt) AS GroupID
FROM
#original_crystal


This is my base data:-
CallId CallEventID StartTime UserId CallEventTypeId CallEventModifiedAt
43517507 1 25/01/2013 00:05:51 13222 11 25/01/2013 00:05:51
43517507 2 25/01/2013 00:05:51 13222 5 25/01/2013 00:05:51
43517507 7 25/01/2013 00:05:51 13222 25 25/01/2013 00:10:22
43517507 12 25/01/2013 00:05:51 13222 6 25/01/2013 00:13:10
43517507 13 25/01/2013 00:05:51 13222 23 25/01/2013 00:13:10
43517507 14 25/01/2013 00:05:51 13176 24 25/01/2013 00:14:24
43517507 15 25/01/2013 00:05:51 13176 5 25/01/2013 00:14:24
43517507 19 25/01/2013 00:05:51 13176 50 25/01/2013 00:24:50
43517507 20 25/01/2013 00:05:51 13176 26 25/01/2013 00:27:04
43517507 21 25/01/2013 00:05:51 13176 27 25/01/2013 00:27:04
43517507 32 25/01/2013 00:05:51 4 52 25/01/2013 00:29:00
43517507 33 25/01/2013 00:05:51 13176 6 25/01/2013 00:29:01
43517507 34 25/01/2013 00:05:51 13176 36 25/01/2013 00:29:01
43517507 35 25/01/2013 00:05:51 4 53 25/01/2013 00:35:16


And this is the output I'm getting:-

CallId UserId Start_Time Stop_Time
43517507 13222 2013-01-25 00:05:50.967 2013-01-25 00:13:09.740
43517507 13176 2013-01-25 00:14:23.960 2013-01-25 00:29:00.810
43517507 4 2013-01-25 00:29:00.080 2013-01-25 00:35:15.920


What I should be getting is this:-

CallId UserID Start_Time Stop_Time
43517507 13222 25/01/2013 00:05:51 25/01/2013 00:13:10
43517507 13176 25/01/2013 00:14:24 25/01/2013 00:27:04
43517507 4 25/01/2013 00:29:00 25/01/2013 00:29:00
43517507 13176 25/01/2013 00:29:01 25/01/2013 00:29:01
43517507 4 25/01/2013 00:35:16 25/01/2013 00:35:16
[/code]

The CTE appears to be ignoring the fact that user 4 appears in the middle of the data for user 13176 and user 4 also appears at the end.

This is now driving me mad and I'm about to chuck the lot out of the window(!)

Can anyone shed any light on this please?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-28 : 05:04:52
For this output..
/*
CallId UserId Start_Time Stop_Time
43517507 13222 2013-01-25 00:05:50.967 2013-01-25 00:13:09.740
43517507 13176 2013-01-25 00:14:23.960 2013-01-25 00:29:00.810
43517507 4 2013-01-25 00:29:00.080 2013-01-25 00:35:15.920

*/
SELECT CallId,
UserId,
MIN(CallEventModifiedAt) AS StartTime,
MAX(CallEventModifiedAt) AS EndTime
FROM @TheTable
GROUP BY
CallId, UserId


--
Chandu
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-28 : 05:18:48
That's what I'm currently getting.
I need to get the last of the tables I just posted.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-28 : 05:22:59
[code]
DECLARE @TheTable TABLE(CallId int, CallEventID int, StartTime datetime, UserId int, CallEventTypeId int, CallEventModifiedAt datetime)
insert into @TheTable
select 43517507, 1, '25/01/2013 00:05:51', 13222, 11, '25/01/2013 00:05:51' union all
select 43517507 ,2, '25/01/2013 00:05:51', 13222, 5, '25/01/2013 00:05:51' union all
select 43517507 ,7, '25/01/2013 00:05:51', 13222, 25, '25/01/2013 00:10:22' union all
select 43517507 ,12, '25/01/2013 00:05:51', 13222, 6, '25/01/2013 00:13:10' union all
select 43517507 ,13, '25/01/2013 00:05:51', 13222, 23,'25/01/2013 00:13:10' union all
select 43517507 ,14, '25/01/2013 00:05:51', 13176, 24, '25/01/2013 00:14:24' union all
select 43517507, 15, '25/01/2013 00:05:51', 13176, 5, '25/01/2013 00:14:24' union all
select 43517507, 19, '25/01/2013 00:05:51', 13176, 50, '25/01/2013 00:24:50' union all
select 43517507, 20, '25/01/2013 00:05:51', 13176, 26, '25/01/2013 00:27:04' union all
select 43517507, 21, '25/01/2013 00:05:51', 13176, 27, '25/01/2013 00:27:04' union all
select 43517507, 32, '25/01/2013 00:05:51' ,4, 52, '25/01/2013 00:29:00' union all
select 43517507, 33, '25/01/2013 00:05:51', 13176, 6, '25/01/2013 00:29:01' union all
select 43517507, 34, '25/01/2013 00:05:51', 13176, 36, '25/01/2013 00:29:01' union all
select 43517507, 35, '25/01/2013 00:05:51', 4, 53, '25/01/2013 00:35:16'

--This is Visakh's post (01/25/2013 : 03:20:54)
SELECT CallId,UserId,MIN(CallEventModifiedAt) AS Start_Time,MAX(CallEventModifiedAt) AS End_Time
FROM @TheTable t
OUTER APPLY (SELECT MIN(CallEventModifiedAt) AS MinDate
FROM @TheTable
WHERE CallEventModifiedAt> t.CallEventModifiedAt
AND UserId <> t.UserId
)t1
GROUP BY CallId,UserId,MinDate
ORDER BY Start_Time
[/code]
This gives you the result which u wanted

--
Chandu
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -