SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cursor issues
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/25/2013 :  02:27:43  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/25/2013 :  03:20:54  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 01/25/2013 03:23:16
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/25/2013 :  03:42:40  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/25/2013 :  03:58:29  Show Profile  Reply with Quote
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 - 01/25/2013 :  04:32:56  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/25/2013 :  04:33:53  Show Profile  Reply with Quote
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 - 01/25/2013 :  05:21:55  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/25/2013 :  05:27:28  Show Profile  Reply with Quote
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 - 01/25/2013 :  05:34:05  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/25/2013 :  06:09:09  Show Profile  Reply with Quote
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 - 01/25/2013 :  06:30:48  Show Profile  Reply with Quote
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.

Edited by - rmg1 on 01/25/2013 06:31:18
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 01/25/2013 :  07:52:17  Show Profile  Reply with Quote
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 - 01/25/2013 :  08:29:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 01/25/2013 :  08:46:01  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/25/2013 :  09:41:32  Show Profile  Reply with Quote
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 - 01/25/2013 :  09:43:55  Show Profile  Reply with Quote
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.

Edited by - rmg1 on 01/25/2013 09:45:27
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3586 Posts

Posted - 01/25/2013 :  10:47:21  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/28/2013 :  04:44:03  Show Profile  Reply with Quote
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

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


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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/28/2013 :  05:04:52  Show Profile  Reply with Quote
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 - 01/28/2013 :  05:18:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/28/2013 :  05:22:59  Show Profile  Reply with Quote

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

This gives you the result which u wanted

--
Chandu

Edited by - bandi on 01/28/2013 05:23:58
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.7 seconds. Powered By: Snitz Forums 2000