[ItIntern3]

Hi all,

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

[e_i_pi]

Just working with the @T1 table, you could do this:

SELECT
	ThisProcess.Number,
	ThisProcess.Start,
	ThisProcess.End,
	NextProcess.Start AS NextStart,
	CASE WHEN NextProcess.Start IS NULL
		THEN NULL
		ELSE NextProcess.Start - ThisProcess.End
	END AS Difference	
FROM @T1 ThisProcess
LEFT OUTER JOIN @T1 NextProcess
	ON NextProcess.Number = ThisProcess.Number

That will get your result, but I believe it can be done without creating the @T1 table. What is the original table schema?

EDIT: look at my next post, where a bug in the query is fixed. Thanks thava for the eagle eyes

This post has been edited by e_i_pi: 20 December 2011 - 04:47 AM

[thava]

e_i_pi i think you miss the +1 or -1 in the query

[e_i_pi]

Ugh, thanks thava, you're right This is the code I meant:

SELECT
	ThisProcess.Number,
	ThisProcess.Start,
	ThisProcess.End,
	NextProcess.Start AS NextStart,
	CASE WHEN NextProcess.Start IS NULL
		THEN NULL
		ELSE NextProcess.Start - ThisProcess.End
	END AS Difference	
FROM @T1 ThisProcess
LEFT OUTER JOIN @T1 NextProcess
	ON NextProcess.Number = ThisProcess.Number + 1

[baavgai]

Add a difference field to @T1 and you can do something like this:

update @T1
	set difference = a.End - b.Start
	from @T1 a
		inner join @T1 b
			on a.RowNumber=b.RowNumber+1

You don't technically need a RowNumber field. You could have a table with Number and NextNumber:

insert into @T1
	select a.Number, (select min(Number) from t where Number>a.Number) as NextNumber
		from t

Having that pair, you can do joins to worry it out the rest.

Of course, you can also just do a select...

select a.Number, a.Start, a.End, a.End - c.Start as Difference
	from t a
		left outer join (
			select b.Number as NextNumber, (select max(Number) from t where Number<b.Number) as Number, b.End
				from t b
		) c on a.Number=c.Number

Hope this helps.

[ItIntern3]

The problem with the whole next process and this process idea in your code is assuming that the process numbers go in perfect sequential order everytime, which is not the case this situation.

Thanks for the help!

[e_i_pi]

ItIntern3, on 20 December 2011 - 12:06 PM, said:

The problem with the whole next process and this process idea in your code is assuming that the process numbers go in perfect sequential order everytime, which is not the case this situation.

So what is the schema of the underlying table? Unless we know the schema, it's going to be difficult to develop a solution, as you're only giving us a theoretical output of @T1

[ItIntern3]

The temp table @T1 has the columns

Number datetime, start datetime, end datetime, difference datetime.

There are 3 table joins that make up @T1,

inner join Hv (nolock) hv on h.HID = hv.HID      
inner join AODV (nolock) aodv on hv.HVID = aodv.HVID
inner join U (nolock) u on hv.UID = u.UID

[e_i_pi]

Okay, I had a similar problem recently, which I posted in this forum. Let's see how this solution goes:

WITH Timeline AS (
	SELECT
		ThisProcess.ID AS ThisProcessID,
		MIN(NextProcess.ID) AS NextProcessID
	FROM @T1 ThisProcess
	LEFT OUTER JOIN @T1 NextProcess ON NextProcess.Start >= ThisProcess.End
	GROUP BY
		ThisProcess.ID
)

SELECT
	ThisProcess.Number,
	ThisProcess.Start,
	ThisProcess.End,
	NextProcess.Start AS NextStart,
	CASE WHEN NextProcess.Start IS NULL
		THEN NULL
		ELSE NextProcess.Start - ThisProcess.End
	END AS Difference	
FROM @T1 ThisProcess
LEFT OUTER JOIN Timeline ON Timeline.ThisProcessID = ThisProcess.ID
LEFT OUTER JOIN @T1 NextProcess ON NextProcess.ID = Timeline.NextProcessID

The WITH statement creates the timeline of processes, so that you can explicitly determine what the ID of the next process is by utilising a LEFT OUTER JOIN to that table. Is that what you're after?

[ItIntern3]

I was able to simplify my original solution to simply joining the temporary table @T1 to itself through the connection:

inner join @T1 (nolock) b on a.RowNumber+1 = b.RowNumber

Through this connection I was able to easily able to subtract the differences in times.