December 9, 2009 at 11:11 am
Would like to replace cursor with while loop in the following stored procedure or at least compare
performance with while loop. Can someone supply me with while loop non-cursor T-SQL statement ?
/*
The purpose of this stored procedure is to:
polulate LocationDateTime column with data (date) where the EventType IN (1),
polulate EndLocationDateTime column with data (date) where the EventType IN (0),
from table dbo.tmpTable1.
select top 4 * from table dbo.tmpTable1:
ItemId, LocationDateTime, EndLocationDateTime, EventType
100, 2009-11-01 07:28:00, NULL, 1
100, 2009-11-01 07:32:00, NULL, 0
100, 2009-11-01 07:59:00, NULL, 1
100, 2009-11-01 08:13:00, NULL, 0
Result after cursor update: (@tmpTable)
ItemId, LocationDateTime, EndLocationDateTime
100, 2009-11-01 07:28:00, 2009-11-01 07:32:00
100, 2009-11-01 07:59:00, 2009-11-01 08:13:00
*/
CREATE PROCEDURE [dbo].[proc_xx]
@iSubsID int,
@dStartDate datetime = Null,
@dEndDate datetime = Null,
@iTimeOffset int = 120,
@iUserID int
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @tmpTable TABLE
(
ItemId VARCHAR(20),
LocationDateTime DATETIME,
EndLocationDateTime DATETIME
)
DECLARE @lUserID INT,
@ldtStartDate DATETIME,
@ldtEndDate DATETIME,
@liSubsID INT,
@liTimeOffset INT,
@ItemId VARCHAR(20),
@LocationDateTime DATETIME,
@EventType INT,
@lvcItemId VARCHAR(9)
SET @lUserID = @iUserID
SET @ldtStartDate = ISNULL(@dStartDate,DATEADD(month,-1,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))
SET @ldtEndDate = ISNULL(@dEndDate,DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
SET @liTimeOffset = @iTimeOffset
IF @iSubsID = -1
BEGIN
SET @liSubsID = NULL
END
ELSE
BEGIN
SET @liSubsID = @iSubsID
END
DECLARE Travel_cursor CURSOR
FOR
SELECT
ItemId,
LocationDatetime,
EventType
FROM
dbo.tmpTable1
ORDER BY
LocationDatetime
OPEN Travel_cursor
FETCH NEXT FROM Travel_cursor INTO
@ItemId,
@LocationDateTime,
@EventType
WHILE @@FETCH_STATUS = 0
BEGIN
IF @EventType IN (1)
BEGIN
INSERT INTO @tmpTable
(
ItemId,
LocationDateTime,
EndLocationDateTime
)
VALUES
(
@ItemId,
@LocationDateTime,
NULL
)
END
ELSE
-- This part updates EndLocationDateTime
BEGIN
UPDATE
@tmpTable
SET
EndLocationDateTime = @LocationDateTime
WHERE
ItemId = @ItemId
AND EndLocationDateTime IS NULL
AND LocationDateTime < @LocationDateTime
END
-- This part supplies value for @LocationDateTime
FETCH NEXT FROM Travel_cursor INTO
@ItemId,
@LocationDateTime,
@EventType
END
-- SELECT * FROM @tmpTable is purely to display the result after cursor update.
-- The result (@tmpTable) is fed into a select statement which uses the difference between
-- LocationDateTime and EndLocationDateTime to populate a column.
SELECT * FROM @tmpTable
CLOSE Travel_cursor
DEALLOCATE Travel_cursor
-- exec proc_xx 47579, '2009/11/01', '2009/11/30 23:59:59', 120, 16899
December 9, 2009 at 11:21 am
If Jeff suggested that you do this, I suggest you figure out how to write the query using a while loop. Give it a shot. If you have problems, post your code and what the problem is, and I'm sure some one will step up to help.
December 9, 2009 at 11:37 am
Hi Lynn, I am hoping to get several different ways (cursor/while loop/etc.) to do same thing, so I can try them all and see which is fastest. The stored procedure is used on a SQL Server Reporting Services server. This stored procedure is part of SSIS package used for several clients to export report to .xls file and ftp to various client folders on ftp server (over 1000 files exported with monthly reports). Since its used for monthy/weekly/daily reports, I am trying to see if can speed up the export report process (especially for monthly reports).
December 9, 2009 at 11:44 am
Is the following order of records guarantteed, or could you get a type 1 with no following type 0, or could you get 2 consecutive type 1's or 0's?
100, 2009-11-01 07:28:00, NULL, 1
100, 2009-11-01 07:32:00, NULL, 0
100, 2009-11-01 07:59:00, NULL, 1
100, 2009-11-01 08:13:00, NULL, 0
December 9, 2009 at 1:00 pm
Hi Lynn, I attached the script to create the table and insert the data.
Please view attachment: script for tmpTable1.rar
December 9, 2009 at 1:06 pm
clive-421796 (12/9/2009)
Hi Lynn, I attached the script to create the table and insert the data.Please view attachment: script for tmpTable1.rar
Before I look, could you at least answer my question?
December 9, 2009 at 1:20 pm
From Lynn:
Is the following order of records guarantteed, or could you get a type 1 with no following type 0, or could you get 2 consecutive type 1's or 0's?
Hi Lynn, I pasted part of the table insert script below
(some places there is 2 consecutive 0's or 1's):
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 07:20:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 07:32:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 07:59:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 08:13:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 10:40:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 10:42:04','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 11:13:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 11:47:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 14:41:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 15:06:01','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 15:36:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 15:44:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 16:43:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 17:03:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 17:23:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 17:25:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 17:27:01','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 18:22:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 03:56:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 05:20:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 05:49:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 06:16:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 06:28:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 06:33:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 06:45:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 07:22:04','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 07:41:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 07:44:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 07:59:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 08:14:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 08:27:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 08:54:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 10:07:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 10:32:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 11:52:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 12:22:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 12:41:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 12:46:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 13:14:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 13:26:04','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-03 06:34:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-03 06:53:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-03 06:56:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-03 06:59:00','','0')
December 9, 2009 at 1:27 pm
Based on your sample table and data:
with CombinedData as (
select
row_number() over (partition by ItemId order by LocationDateTime asc) as RowNum,
ItemId,
LocationDateTime,
EndLocationDateTime,
EventType
from
dbo.tmpTable1
)
select
cd1.ItemId,
cd1.LocationDateTime,
cd2.LocationDateTime as EndLocationDateTime
from
CombinedData cd1
left outer join CombinedData cd2
on (cd1.ItemId = cd2.ItemId
and cd1.RowNum = cd2.RowNum - 1)
where
cd1.EventType = 1
December 9, 2009 at 1:31 pm
Lynn Pettis (12/9/2009)
Based on your sample table and data:
with CombinedData as (
select
row_number() over (partition by ItemId order by LocationDateTime asc) as RowNum,
ItemId,
LocationDateTime,
EndLocationDateTime,
EventType
from
dbo.tmpTable1
)
select
cd1.ItemId,
cd1.LocationDateTime,
cd2.LocationDateTime as EndLocationDateTime
from
CombinedData cd1
left outer join CombinedData cd2
on (cd1.ItemId = cd2.ItemId
and cd1.RowNum = cd2.RowNum - 1)
where
cd1.EventType = 1
I was just getting ready to post a similar solution when I hit refresh to verify. Lo and behold Lynn beat me to the punch.
The short of it, there shouldn't be a reason to use RBAR for this query when set based will work.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 9, 2009 at 1:45 pm
clive-421796 (12/9/2009)
From Lynn:Is the following order of records guarantteed, or could you get a type 1 with no following type 0, or could you get 2 consecutive type 1's or 0's?
Hi Lynn, I pasted part of the table insert script below
(some places there is 2 consecutive 0's or 1's):
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 07:20:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 07:32:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 07:59:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 08:13:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 10:40:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 10:42:04','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 11:13:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 11:47:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 14:41:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 15:06:01','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 15:36:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 15:44:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 16:43:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 17:03:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 17:23:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 17:25:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-01 17:27:01','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-01 18:22:02','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 03:56:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 05:20:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 05:49:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 06:16:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 06:28:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 06:33:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 06:45:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 07:22:04','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 07:41:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 07:44:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 07:59:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 08:14:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 08:27:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 08:54:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 10:07:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 10:32:03','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 11:52:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 12:22:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 12:41:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-02 12:46:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 13:14:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-02 13:26:04','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-03 06:34:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-03 06:53:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-03 06:56:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-03 06:59:00','','0')
Didn't really answer the question. For each ItemId, is the order consistant? Your cursor code runs through the data top to bottom, but it checks the ItemId when doing the update indicating that the data should be sorted by ItemID, LocationDateTime. If sorted this way, will you always have a 1 followed by a 0? Is it possible to have a 1 with no subsequent 0?
December 9, 2009 at 2:08 pm
Hi Lynn, you're spot on, for the same ItemId there should be 1, 0, 1, 0.
View below:
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 03:54:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 05:27:02','','0')
Where there is a 1,1 or 0,0 its because there is an overlap of ItemId's, view below:
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-05 05:55:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 05:59:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 06:00:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-05 06:01:01','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-05 06:03:01','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 06:13:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-05 06:13:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 06:14:00','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-05 06:16:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 06:27:00','','1')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('5158296','2009-11-05 06:31:01','','0')
INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)
VALUES ('412061','2009-11-05 06:35:00','','0')
December 9, 2009 at 2:16 pm
Hi Lynn, thanks for your speedy reply and solution. Much appreciated. Will test and also compare runtime of cursor vs your script, add the comparison results to this forum.
December 9, 2009 at 2:30 pm
I can't review this now, but did you try the version of code I provided on the other post you had for this?
select itemid
,max(case eventtype when 1 then locationtime else null end) as starttime
,max(case eventtype when 0 then locationtime else null end) as endtime
from #temp
group by itemid
You may need min for start time if multiples are possible.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 9, 2009 at 6:23 pm
Lynn Pettis (12/9/2009)
If Jeff suggested that you do this, I suggest you figure out how to write the query using a while loop. Give it a shot. If you have problems, post your code and what the problem is, and I'm sure some one will step up to help.
Just to be clear... Clive suggested that he wanted to replace a Cursor with a While Loop on the other post. My suggestion was that if Clive wanted to replace the Cursor code with While Loop code, that he shouldn't bother because a Forward_Only, Read_Only Cursor would do just as well.
I also suggested that he post it on a T-SQL forum instead of an article thread to get better help considering that there are also other problems in the code besides the RBAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2009 at 6:28 pm
Jeff Moden (12/9/2009)
Lynn Pettis (12/9/2009)
If Jeff suggested that you do this, I suggest you figure out how to write the query using a while loop. Give it a shot. If you have problems, post your code and what the problem is, and I'm sure some one will step up to help.Just to be clear... Clive suggested that he wanted to replace a Cursor with a While Loop on the other post. My suggestion was that if Clive wanted to replace the Cursor code with While Loop code, that he shouldn't bother because a Forward_Only, Read_Only Cursor would do just as well.
I also suggested that he post it on a T-SQL forum instead of an article thread to get better help considering that there are also other problems in the code besides the RBAR.
Okay, clear. Still not going to write a WHILE loop psudo cursor when there is a better, set-based way to accomplish the same task. He wants a WHILE loop, he needs to try writing it. No problem helping, just not doing. 😛
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply