October 28, 2003 at 9:14 pm
I have a table containing the status history of workorders. I need to be able to enter a data and return the status of those workorders at that point in time. I am currently using a cursor and looping through the table to return the top 1 results for each workorder. Is there a faster way to do this.
We are currently running on MSSQL7 (upgrading to SQL2k in a few months). Below is a portion of the code used. The data is then queried to produce reports
There are multiple entries per workorder number, all dated. Due to the program we use only inserting date/time data to the nearest second, I also need to use the rowstamp to determine the most recent entry.
DECLARE @EndDate DATETIME
SET @EndDate = ’22-Oct-2003 23:59’
CREATE TABLE #StatsTemp (wonum varchar(20), status varchar(16), changedate datetime)
--Wipes out any workorders after the given date
DECLARE Stats CURSOR READ_ONLY FOR
SELECT DISTINCT wonum
FROM wostatus
WHERE changedate < @EndDate
OPEN WoStats
FETCH NEXT FROM WoStats INTO @WoNum
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #StatsTemp (wonum, status, changedate)
SELECT TOP 1 wonum, status, changedate
FROM wostatus
WHERE changedate < @EndDate AND wonum = @WoNum
ORDER BY changedate desc, rowstamp desc
FETCH NEXT FROM WoStats INTO @WoNum
END
CLOSE WoStats
DEALLOCATE WoStats
Any suggestions would be greatly appreciated.
Nigel H.
Software Engineer
RLM Systems Pty Ltd
Nigel H.
Infrastructure
Lockheed Martin Australia
October 29, 2003 at 6:57 am
If RowStamp is a unique incrementing value (e.g. has the identity property), why not just something like this?
SELECT s.WONum, s.Status, s.ChangeDate
FROM WOStatus s JOIN
(SELECT WONum, MAX(RowStamp) RowStamp
FROM WOStatus
WHERE ChangeDate < @EndDate
GROUP BY WONum) m ON s.WONum = m. WONum AND s.Rowstamp = m.RowStamp
--Jonathan
--Jonathan
October 29, 2003 at 5:34 pm
Thanks for that Jonathon.
The rowstamp field is a timestamp.
I did try using just the rowstamp in my testing, but soon discovered that some entries were inserted out of sequence rowstamp wise, but the changedate was correct (there might only be 20 cases like this out of 90,000+ records), but then there were times when the changedate was identical (to the nearest minute), but the rowstamp reflects the correct order.
The code that you supplied has improved speed incredibly, from 2mins to about 6secs on our server with about 100 processes running at the time.
Thanks again for your reply
Nigel H.
Software Engineer
RLM Systems Pty Ltd
Nigel H.
Infrastructure
Lockheed Martin Australia
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply