T-sql question - 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)

T-sql question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-06-25 : 13:31:05

The follow is the data I have in a single table:

createdate IDcolumn Custnumber
12/1/2005 2 100
5/1/2007 1 100
5/29/2007 2 100
5/30/2007 3 100
6/1/12007 4 100
6/28/2007 5 100
2/1/2006 2 200
6/1/20007 1 200
6/5/2007 2 200
6/7/2007 3 200
2/5/2005 4 300
6/10/2008 1 300

For the above data, if the first row's createdate is less than second row's createdate for the same custnumber 100,
then the IDcolumn of the first row should be updated to 1, and accordingly if the second row's createdate is less than
3rd row, then second row's IDcolumn should be 2 etc., the following is the output I am expecting:

createdate IDcolumn Custnumber
12/1/2005 1 100
5/1/2007 2 100
5/29/2007 3 100
5/30/2007 4 100
6/1/12007 5 100
6/28/2007 6 100
2/1/2006 1 200
6/1/20007 2 200
6/5/2007 3 200
6/7/2007 4 200
2/5/2005 1 300
6/10/2008 2 300

Any ideas are greatly appreciated, Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 13:34:43

[code]UPDATE t
SET t.IDColumn=(SELECT COUNT(*) FROM YourTable WHERE CustNumber=t.CustNumber
AND createdate <t.createdate) +1
FROM YourTable t[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 13:39:25

Where do you want to show data?
If you use reports, group the report by Custnumber and reset recordnumber for each group

Madhivanan

Failing to plan is Planning to fail

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-06-25 : 16:35:41

quote:
Originally posted by madhivanan

Where do you want to show data?
If you use reports, group the report by Custnumber and reset recordnumber for each group

Madhivanan

Failing to plan is Planning to fail

Actually I need to update the table itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources