Compare to next record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Compare to next record

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-11-22 : 16:10:37

I am looking for a way to write a query that will allow me to compare records and accordingly assign a value.

For example:

I have Table A which has the following columns:
ExtractDate,
ID,
HireDate,
StatusCode
I need to write a query that will build TABLE B with columns:
ID,
HireDate,
StatusCode,
StatusStartDate,
StatusEndDate

The query needs to look at all the values for a Table A.ID and determine when the Table A.Status changed. The query needs to then insert a record into Table B with a record for each of the distinct StatusCode values in Table A.

Therefore, in this sample data we see:
Extracted ID HIREDATE STATUS
20030715 001 20010918 A
20030815 001 20010918 A
20030915 001 20010918 A
20031015 001 20010918 P
20031118 001 20010918 P
20031214 001 20010918 P
20040115 001 20010918 A

I would want the following in Table B to show up:
ID HIREDATE STATUS STATUSSTART STATUSEND
001 20010918 A 20030715 20031015
001 20010918 P 20031015 20040115
001 20010918 A 20040115

Thanks fr any help on this.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-22 : 17:08:12

There really is no "next". The order of rows in a database is meaningless...well, except for the clustered index...you need to order the data..

You could do SELECT * FROM yourTable

And you could get different results

You need something that relates the rows of data to each other.

Got anything like that?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources