I am presented with a set of data in which I need information from multiple lines in each line of my query. An example will simplify my problem.
Example: Each row has a number (indicating the process number), the starting time of the process, the ending time of the process, and the difference between the end of the current process and the start of the next process.
Number Start End Difference
135 11:30am 11:40am (11:40am - 12:00pm)
136 12:00pm 12:30pm (12:03pm - 1:00pm)
137 1:00pm 11:15pm ...
I have this code working, however, am very interested in knowing if there is a better way to do it. I am currently putting the query retrieving the Number, start and end columns into a temporary table variable (@T1), then adding another a column which labels the row numbers to @T1. @T1 now looks like this:
Number Start End RowNumber
135 11:30am 11:40am 1
136 12:00pm 12:30pm 2
137 1:00pm 11:15pm 3
I then create a second table variable (@T2), and insert rows from @T1 (keeping track of the current row in variable @CurrentRow then incrementing @CurrentRow at the end of the while loop--and looping while the make shift cursor is not at the last row of @T1). My while loop code is very similar to the following:
while @CurrentRow < @RowCount begin insert into @T2(...) select Number, start, end, (select end from @T1 where RowNumber = @CurrentRow) - (select start from @T1 where RowNumber = @CurrentRow+1) as difference from @T1 where @CurrentRow = RowNumber set @CurrentRow = @CurrentRow + 1 end
again, the purpose of the difference column is to get the difference in current process end time and the next process start time. My basic problem here is efficiency in row level access within SQL. I have a feeling there is a better way to accomplish this, and being a very inexperienced problem solver in SQL, I would appreciate some insight on a more efficient solution.
Thanks in advance!
This post has been edited by ItIntern3: 19 December 2011 - 04:00 PM