Group into a cursor and add new columns

I would like to within a Stored Procedure Query a table and qroup the dates, add columns to the cursor to
1-count the rows in the group, 2-number the group.

So, if there is 4 different dates and the records in that date group has 10 rows:
**Group by TESTDATE**
Stabilitydate25_reads_id Forcastdate Testread Testdate Stabilitydata25_id Group GroupRow
111 Null 123123 2007-06-04 xxx1 643 1 1
112 ... 44534 ... 643 1 2
113 Null 2342 ... 643 1 3
... ... ... ... ... ... ...
345 Null 76878 2008-02-07 xxx1 643 2 1
... ... ... ... ... ... ...
890 Null 459234 2008-03-14 xxx1 643 3 1
...
Like that ect in a Cursor from the attached table.

My efforts have not produced the results I expected and would really Appreciate some assitance
Thanks
stab-layout.jpg

Microsoft SQL Server 2005

Last Comment

Wayne Burr

8/22/2022 - Mon

elimesika

7/16/2008

HI
Please paste you current SQL syntax here

Wayne Burr

7/16/2008

ASKER

Based on my task ahead of me I'm not sure the exact direction I'm taking is the correct one yet. The idea was to take a SP with cursors and come up with the breakdown of each of the dates. There is more work to be done once I get the results. Here is as far as I got with building of SQL:

SELECT STABILITYDATA25_READS_ID,TESTDATE, row_number() over
(partition by TESTDATE order by STABILITYDATA25_READS_ID) as xrowcount
FROM STABLITYDATAR25_READS
where STABILITYDATA25_ID = 643

With the above SQL I still need to have it numbered for each group. It numbers the rows like I need but I still can't get the groups. There should be two goups for the above example.
stab-grouping.jpg

elimesika

7/16/2008

HI again

You need to use cursors and a temp table, check this code ...

create table #temptable (
id int,
test_date datetime,
rows int,
group_number int)
 
 
DECLARE @id int
DECLARE @test_date datetime
DECLARE @rows int
DECLARE @group_number int
DECLARE @last_test_date datetime
 
	
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT STABILITYDATA25_READS_ID,TESTDATE
FROM STABLITYDATAR25_READS
WHERE STABILITYDATA25_ID = 643
ORDER BY STABILITYDATA25_READS_ID
 
OPEN c1
 
FETCH NEXT FROM c1
INTO @id, @test_date
 
set @rows = 1
set @group_number = 1
@last_test_date = @test_date
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
        insert into #temptable values (@id, @test_date,@rows,@group_number)        
	FETCH NEXT FROM c1
	INTO @id, @test_date
	if (@test_date = @last_test_date)
	  begin
	     set @rows = @rows + 1
	  end
	else
	  begin
	      set @rows = 1
	      set @group_number = @group_number + 1
	  end
	set @last_test_date = @test_date
 
END
CLOSE c1
DEALLOCATE c1
 
select * from #temptable