We help IT Professionals succeed at work.

Group into a cursor and add new columns

wayneburr
wayneburr asked
on
196 Views
Last Modified: 2011-10-19
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
Comment
Watch Question

HI
Please paste you current SQL syntax here

Author

Commented:
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
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

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I was able to use your suggestion into my SP and works great!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.