[RESOLVED] Compare rows of a record set @ sp (SQL Server)

[Chathura]

Hi,
I'm using ms sqlserver 2000. I want to write a stored procedure and it must do following
1. Select certain records from a table using a where clause
2. compare two fields of two records (say F1 of row 1 with F2 of row2)
3. update another column (say F3 of row2)
4. Use a loop to continue these 2 & 3 steps

Please help me to do this. Any suggesion will be highly appreciated.

[szlamany]

Well - that sounds like a need for a cursor loop in the SPROC - which of course most people try to avoid.

Show us some sample data - what would row 1 and row 2 actually look like?

Show us enough rows of data so we can see this F1 and F2 relationship.

Is it only two pairs of rows that "compare"?

[Chathura]

Show us enough rows of data so we can see this F1 and F2 relationship.

Thanks szlamany for your reply.

Show us some sample data - what would row 1 and row 2 actually look like?

See these rows
=========Col1================Col2==============Col3
Row1 ==12/12/2007 08:00:00 ==12/12/2007 10:00:00 ===0
Row2 ==12/12/2007 11:00:00 ==12/12/2007 13:00:00 ===1(11:00:00-10:00:00)
Row3 ==12/12/2007 15:00:00 ==12/12/2007 16:00:00 ===2
Row4 ==12/12/2007 16:00:00 ==12/12/2007 17:00:00 ===0

Is it only two pairs of rows that "compare"?

No. I have more than two rows.
**Col3 is the column which should be updated after comparing Row2 Col1 and Row1Col2 with the difference of the two values.

[szlamany]

Here's a way to do it without storing col3 at all...

Set NoCount On
-- Temp table to fool with
Create Table #TempTbl (Col1 datetime, Col2 datetime)
 
-- Your data
Insert into #TempTbl values('12/12/2007 08:00:00','12/12/2007 10:00:00')
Insert into #TempTbl values('12/12/2007 11:00:00','12/12/2007 13:00:00')
Insert into #TempTbl values('12/12/2007 15:00:00','12/12/2007 16:00:00')
Insert into #TempTbl values('12/12/2007 16:00:00','12/12/2007 17:00:00')
 
-- A quick Select to show the data
Select * From #TempTbl
 
-- Now here's a Select that joins back to the table itself
-- and joins to the prior row!
Select * From #TempTbl T1
   Left Join #TempTbl T2 on T2.Col2=(Select Max(T3.Col2) From #TempTbl T3
                        Where T3.Col2<T1.Col2)
 
-- And now your final output - all without ever storing the Col3 value
-- instead calc'ing it at query time
 
Select T1.Col1,T1.Col2,IsNull(DateDiff(hh,T2.Col2,T1.Col1),0) From #TempTbl T1
   Left Join #TempTbl T2 on T2.Col2=(Select Max(T3.Col2) From #TempTbl T3
                        Where T3.Col2<T1.Col2)
 
 
Drop Table #TempTbl

Col1                     Col2                                                   
------------------------ ------------------------------------------------------ 
2007-12-12 08:00:00.000  2007-12-12 10:00:00.000
2007-12-12 11:00:00.000  2007-12-12 13:00:00.000
2007-12-12 15:00:00.000  2007-12-12 16:00:00.000
2007-12-12 16:00:00.000  2007-12-12 17:00:00.000
 
Col1                     Col2                     Col1                     Col2                                                   
------------------------ -----------------------  ------------------------ ------------------------------------------------------ 
2007-12-12 08:00:00.000  2007-12-12 10:00:00.000  NULL                     NULL
2007-12-12 11:00:00.000  2007-12-12 13:00:00.000  2007-12-12 08:00:00.000  2007-12-12 10:00:00.000
2007-12-12 15:00:00.000  2007-12-12 16:00:00.000  2007-12-12 11:00:00.000  2007-12-12 13:00:00.000
2007-12-12 16:00:00.000  2007-12-12 17:00:00.000  2007-12-12 15:00:00.000  2007-12-12 16:00:00.000
 
Col1                     Col2                       
------------------------ ------------------------ ----------- 
2007-12-12 08:00:00.000  2007-12-12 10:00:00.000  0
2007-12-12 11:00:00.000  2007-12-12 13:00:00.000  1
2007-12-12 15:00:00.000  2007-12-12 16:00:00.000  2
2007-12-12 16:00:00.000  2007-12-12 17:00:00.000  0

[Chathura]

Thanks a lot "szlamany". I got through with the problem with your help. Thanks again. your help must be appreciate more than once because I couldn't find any others help other than you.

[szlamany]

I am glad it worked for you.

I enjoy the challenges that people post here - using SQL to solve a problem with "set-based" logic is always a great thing to do!