| Author |
 Topic  |
|
rmg1
Constraint Violating Yak Guru
253 Posts |
 Posted - 01/25/2013 : 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
India
48037 Posts |
Posted - 01/25/2013 : 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/
|
Edited by - visakh16 on 01/25/2013 03:23:16 |
 |
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/25/2013 : 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
Posted - 01/25/2013 : 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/
|
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/25/2013 : 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
Posted - 01/25/2013 : 04:33:53
|
hmm..what are indexes you've on #original_crystal? how big is the dataset?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/25/2013 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
Posted - 01/25/2013 : 05:27:28
|
check execution plan and see what are costly steps
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/25/2013 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/25/2013 : 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. |
Edited by - rmg1 on 01/25/2013 06:31:18 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 01/25/2013 : 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. |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/25/2013 : 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
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 01/25/2013 : 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; |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48037 Posts |
Posted - 01/25/2013 : 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/
|
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/25/2013 : 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. |
Edited by - rmg1 on 01/25/2013 09:45:27 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 01/25/2013 : 10:47:21
|
| You are very welcome - glad to be of help. |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/28/2013 : 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
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? |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1694 Posts |
Posted - 01/28/2013 : 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 |
|
|
|
rmg1
Constraint Violating Yak Guru
253 Posts |
Posted - 01/28/2013 : 05:18:48
|
That's what I'm currently getting.
I need to get the last of the tables I just posted. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1694 Posts |
Posted - 01/28/2013 : 05:22:59
|
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 |
|
|
|
Topic |
|
Cursor issues
