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.
Author |
Topic |
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-25 : 02:27:43
|
Hi allBefore 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 CallEventModifiedAt43494439 1 10853 23/01/2013 00:00:2143494439 2 10853 23/01/2013 00:00:2143494439 7 10853 23/01/2013 00:03:3143494439 11 10853 23/01/2013 00:04:3543494439 12 10853 23/01/2013 00:05:1743494439 13 10853 23/01/2013 00:05:1743494439 14 4070 23/01/2013 00:06:2543494439 15 4070 23/01/2013 00:06:2543494439 20 4070 23/01/2013 00:19:4443494439 21 4070 23/01/2013 00:19:4443494439 24 4070 23/01/2013 00:21:2043494439 29 4 23/01/2013 00:23:0343494439 30 4070 23/01/2013 00:23:3043494439 31 4070 23/01/2013 00:23:3043494439 32 4 23/01/2013 00:23:48 This is my output:-CallId UserId Start_Time Stop_Time43494439 10853 23/01/2013 00:00:21 23/01/2013 00:05:1743494439 4070 23/01/2013 00:06:25 23/01/2013 00:23:3043494439 4 23/01/2013 00:23:03 23/01/2013 00:23:4843494439 4070 23/01/2013 00:23:30 23/01/2013 00:23:3043494439 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 UserIDSo, 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 intdeclare @Start_time_old datetimedeclare @callid varchar(15)declare @userid intdeclare @Start_time datetimedeclare loop_cursor cursor for select callid ,userid ,calleventmodifiedat from #original_crystal order by callid ,calleventmodifiedatopen loop_cursorfetch next from loop_cursorinto @callid, @userid, @start_timeinsert into #temp_times (callid ,UserId ,Start_Time )values (@callid ,@userid ,@Start_time )while @@FETCH_STATUS=0begin 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_cursorinto @callid, @userid, @start_timeendupdate #temp_times set stop_time=@Start_time_old where callid=@callid and UserId=@userid_oldclose loop_cursordeallocate 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 belowSELECT CallId,UserId,MIN(CallEventModifiedAt) AS Start_Time,MAX(CallEventModifiedAt) AS End_TimeFROM #original_crystal tOUTER APPLY (SELECT MIN(CallEventModifiedAt) AS MinDate FROM #original_crystal WHERE CallEventModifiedAt> t.CallEventModifiedAt AND UserId <> t.UserId )t1GROUP BY CallId,UserId,MinDate ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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. |
|
|
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. |
|
|
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 CallEventModifiedAt43505928 1 24/01/2013 00:01:23 11776 11 24/01/2013 00:01:2343505928 2 24/01/2013 00:01:23 11776 5 24/01/2013 00:01:2343505928 7 24/01/2013 00:01:23 11776 25 24/01/2013 00:05:0743505928 11 24/01/2013 00:01:23 11776 50 24/01/2013 00:06:5343505928 12 24/01/2013 00:01:23 11776 50 24/01/2013 00:07:1143505928 13 24/01/2013 00:01:23 11776 6 24/01/2013 00:07:4543505928 14 24/01/2013 00:01:23 11776 23 24/01/2013 00:07:4543505928 15 24/01/2013 00:01:23 6737 24 24/01/2013 00:07:5643505928 16 24/01/2013 00:01:23 6737 5 24/01/2013 00:07:5643505928 19 24/01/2013 00:01:23 6737 26 24/01/2013 00:18:5343505928 20 24/01/2013 00:01:23 6737 27 24/01/2013 00:18:5343505928 23 24/01/2013 00:01:23 6737 6 24/01/2013 00:19:0243505928 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_Time43505928 11776 24/01/2013 00:01:23 24/01/2013 00:01:2343505928 11776 24/01/2013 00:05:07 24/01/2013 00:05:0743505928 11776 24/01/2013 00:06:53 24/01/2013 00:06:5343505928 11776 24/01/2013 00:07:11 24/01/2013 00:07:1143505928 11776 24/01/2013 00:07:45 24/01/2013 00:07:4543505928 6737 24/01/2013 00:07:56 24/01/2013 00:07:5643505928 6737 24/01/2013 00:18:53 24/01/2013 00:18:5343505928 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:23UserID 11776 - Stop Time 24/01/2013 00:07:45UserID 6737 - Start Time 24/01/2013 00:07:56UserID 6737 - Stop Time 24/01/2013 00:19:02 |
|
|
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 EndTimeFROM CTEGROUP BY CallId, UserId, GroupId; |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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. |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-28 : 04:44:03
|
Hi allUnfortunately, 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 CallEventModifiedAt43517507 1 25/01/2013 00:05:51 13222 11 25/01/2013 00:05:5143517507 2 25/01/2013 00:05:51 13222 5 25/01/2013 00:05:5143517507 7 25/01/2013 00:05:51 13222 25 25/01/2013 00:10:2243517507 12 25/01/2013 00:05:51 13222 6 25/01/2013 00:13:1043517507 13 25/01/2013 00:05:51 13222 23 25/01/2013 00:13:1043517507 14 25/01/2013 00:05:51 13176 24 25/01/2013 00:14:2443517507 15 25/01/2013 00:05:51 13176 5 25/01/2013 00:14:2443517507 19 25/01/2013 00:05:51 13176 50 25/01/2013 00:24:5043517507 20 25/01/2013 00:05:51 13176 26 25/01/2013 00:27:0443517507 21 25/01/2013 00:05:51 13176 27 25/01/2013 00:27:0443517507 32 25/01/2013 00:05:51 4 52 25/01/2013 00:29:0043517507 33 25/01/2013 00:05:51 13176 6 25/01/2013 00:29:0143517507 34 25/01/2013 00:05:51 13176 36 25/01/2013 00:29:0143517507 35 25/01/2013 00:05:51 4 53 25/01/2013 00:35:16And this is the output I'm getting:-CallId UserId Start_Time Stop_Time43517507 13222 2013-01-25 00:05:50.967 2013-01-25 00:13:09.74043517507 13176 2013-01-25 00:14:23.960 2013-01-25 00:29:00.81043517507 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_Time43517507 13222 25/01/2013 00:05:51 25/01/2013 00:13:1043517507 13176 25/01/2013 00:14:24 25/01/2013 00:27:0443517507 4 25/01/2013 00:29:00 25/01/2013 00:29:0043517507 13176 25/01/2013 00:29:01 25/01/2013 00:29:0143517507 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? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-28 : 05:04:52
|
For this output../*CallId UserId Start_Time Stop_Time43517507 13222 2013-01-25 00:05:50.967 2013-01-25 00:13:09.74043517507 13176 2013-01-25 00:14:23.960 2013-01-25 00:29:00.81043517507 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 EndTimeFROM @TheTableGROUP BY CallId, UserId --Chandu |
|
|
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. |
|
|
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 @TheTableselect 43517507, 1, '25/01/2013 00:05:51', 13222, 11, '25/01/2013 00:05:51' union allselect 43517507 ,2, '25/01/2013 00:05:51', 13222, 5, '25/01/2013 00:05:51' union allselect 43517507 ,7, '25/01/2013 00:05:51', 13222, 25, '25/01/2013 00:10:22' union allselect 43517507 ,12, '25/01/2013 00:05:51', 13222, 6, '25/01/2013 00:13:10' union allselect 43517507 ,13, '25/01/2013 00:05:51', 13222, 23,'25/01/2013 00:13:10' union allselect 43517507 ,14, '25/01/2013 00:05:51', 13176, 24, '25/01/2013 00:14:24' union allselect 43517507, 15, '25/01/2013 00:05:51', 13176, 5, '25/01/2013 00:14:24' union allselect 43517507, 19, '25/01/2013 00:05:51', 13176, 50, '25/01/2013 00:24:50' union allselect 43517507, 20, '25/01/2013 00:05:51', 13176, 26, '25/01/2013 00:27:04' union allselect 43517507, 21, '25/01/2013 00:05:51', 13176, 27, '25/01/2013 00:27:04' union allselect 43517507, 32, '25/01/2013 00:05:51' ,4, 52, '25/01/2013 00:29:00' union allselect 43517507, 33, '25/01/2013 00:05:51', 13176, 6, '25/01/2013 00:29:01' union allselect 43517507, 34, '25/01/2013 00:05:51', 13176, 36, '25/01/2013 00:29:01' union allselect 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_TimeFROM @TheTable tOUTER APPLY (SELECT MIN(CallEventModifiedAt) AS MinDate FROM @TheTable WHERE CallEventModifiedAt> t.CallEventModifiedAt AND UserId <> t.UserId )t1GROUP BY CallId,UserId,MinDateORDER BY Start_Time[/code]This gives you the result which u wanted--Chandu |
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|