asked on

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

elimesika

HI
Please paste you current SQL syntax here

Wayne Burr

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

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

Open in new window

ASKER CERTIFIED SOLUTION

elimesika

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Burr

ASKER

I was able to use your suggestion into my SP and works great!