SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 reset row number/identity based on another field?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gypsymami
Starting Member

USA
3 Posts

Posted - 12/19/2007 :  17:32:45  Show Profile  Reply with Quote
I'm not sure if this is even possible but if so, please help!

I need a counter on a table that will reset based on another field. Basically, I would like the row count (id field, whatever the name) to return to 1 when the ID field changes. What I am trying to do is count the number of dates that is associated with each ID.

Right now, I have this:
Row Date ID
1 2007-09-01 00:00:00.000 277
2 2007-10-01 00:00:00.000 277
3 2007-11-01 00:00:00.000 277
4 2007-12-01 00:00:00.000 277
5 2008-01-01 00:00:00.000 277
6 2007-10-01 00:00:00.000 293
7 2007-11-01 00:00:00.000 293
8 2007-12-01 00:00:00.000 293
9 2008-01-01 00:00:00.000 293


What i would LIKE is this:
Row Date ID
1 2007-09-01 00:00:00.000 277
2 2007-10-01 00:00:00.000 277
3 2007-11-01 00:00:00.000 277
4 2007-12-01 00:00:00.000 277
5 2008-01-01 00:00:00.000 277
1 2007-10-01 00:00:00.000 293
2 2007-11-01 00:00:00.000 293
3 2007-12-01 00:00:00.000 293
4 2008-01-01 00:00:00.000 293
... and so on

I'm using the SQL below because that's the only way i could figure to get the counter in there. Of course, it doesn't give me what a want
drop table #table
select identity (int,1,1) as row , date,id
into #table
from [TableList]
where date is not null and ID in ( 277, 293)
order by date
select * from #table group by id, ratingdate, row order by ratingdate

Any assistance would be greatly appreciated

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 12/19/2007 :  17:44:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Fourth time today!
-- Prepare sample data
DECLARE	@Sample TABLE (Row INT, Date DATETIME, ID INT)

INSERT	@Sample
SELECT	1, '2007-09-01', 277 UNION ALL
SELECT	2, '2007-10-01', 277 UNION ALL
SELECT	3, '2007-11-01', 277 UNION ALL
SELECT	4, '2007-12-01', 277 UNION ALL
SELECT	5, '2008-01-01', 277 UNION ALL
SELECT	6, '2007-10-01', 293 UNION ALL
SELECT	7, '2007-11-01', 293 UNION ALL
SELECT	8, '2007-12-01', 293 UNION ALL
SELECT	9, '2008-01-01', 293

-- Initialize staging area
DECLARE	@Stage TABLE (RowID INT IDENTITY(1, 1), Date DATETIME, ID INT)

INSERT		@Stage
SELECT		Date,
		ID
FROM		@Sample
ORDER BY	ID,
		Date

-- Show the expected output
SELECT		s.RowID - e.MinID + 1 AS Row,
		s.Date,
		s.ID
FROM		(
			SELECT		ID,
					MIN(RowID) AS MinID
			FROM		@Stage
			GROUP BY	ID
		) AS e
INNER JOIN	@Stage AS s ON s.ID = e.ID
ORDER BY	s.ID,
		s.RowID



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gypsymami
Starting Member

USA
3 Posts

Posted - 12/19/2007 :  18:12:08  Show Profile  Reply with Quote
I may be misunderstanding something - or maybe not explaining correctly. But, the table has THOUSANDS of IDs and Thousands of dates. From the looks of this, i'd basically be manually writing the table. Correct?

I don't want to do this. I am hoping to find a way to change the counter based on the ID field.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 12/19/2007 :  18:15:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't think I have access to your database. Am I right?
If I don't have access to your database, I must somehow have some sample data, right?

So I wrote the "Prepare sample data" part in the code above since I do not have access to your database.

What you need, is the "Initialize staging area" and "Show the expected output" parts.
Copy those two parts, change the table names and column names to the one used in your environment.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 12/19/2007 :  18:16:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Initialize staging area
DECLARE	@Stage TABLE (RowID INT IDENTITY(1, 1), Date DATETIME, ID INT)

INSERT		@Stage
SELECT		Date,
		ID
FROM		{Your table name here}
ORDER BY	ID,
		Date

-- Show the expected output
SELECT		s.RowID - e.MinID + 1 AS Row,
		s.Date,
		s.ID
FROM		(
			SELECT		ID,
					MIN(RowID) AS MinID
			FROM		@Stage
			GROUP BY	ID
		) AS e
INNER JOIN	@Stage AS s ON s.ID = e.ID
ORDER BY	s.ID,
		s.RowID



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gypsymami
Starting Member

USA
3 Posts

Posted - 12/20/2007 :  11:33:13  Show Profile  Reply with Quote
beautiful. Thanks a million!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 12/20/2007 :  11:54:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You're welcome.
Please come back when you experience next difficulty.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000