Count and Update?
-
Any ideas?
I have a table that has the following fields:
Area, Age, Sex, Name, Title, RegNumber
Currently the Reg_Number field is empty for each record, but I need to somehow be able to count each person that belongs to each group defined by area, age, sex and title and assign them a number from 1-? in order and update the Reg_number field with that number for each record.
In other words, I will have for example, numbered records (say 1-10) for each group defined by Area, Age, Sex, and Title starting the numbering over for each different grouping… Thanx for the help… as usual…..
-
Hitek, a few questions...
1. Make sure I understand...
1, 40, 'M', 'Bob', 'Mr', ''
1, 40, 'M', 'John', 'Mr', ''
1, 40, 'F', 'Betty', 'Mrs', ''
1, 40, 'F', 'Jane', 'Mrs', ''
Becomes
1, 40, 'M', 'Bob', 'Mr', '1'
1, 40, 'M', 'John', 'Mr', '2'
1, 40, 'F', 'Betty', 'Mrs', '1'
1, 40, 'F', 'Jane', 'Mrs', '2'
2. Is this a one-time deal, or ongoing thing?
3. If "ongoing", will table have some records "correct" while others have blank RegNumber? or redo entire thing from scratch again?
4. Does it matter if Regnumbers are consecutive integers?
Once you understand the BITs, all the pieces come together
-
declare @Area, @Age, @Sex, @Title
declare @i int
declare group_cursor cursor for select distinct Area, Age, Sex, Title from table
open group_cursor
Fetch next from group_cursor into @Area, @Age, @Sex, @Title
while @@fetch_status = 0
begin
set @i = 0
update table set @i = RegNumber = @i + 1
Fetch next from group_cursor into @Area, @Age, @Sex, @Title
end
close group_cursor
deallocate group_cursor
go
You may have to finish it up.
-
ThomasH,
thanx for the response... You are correct in assuming the following:
1, 40, 'M', 'Bob', 'Mr', ''
1, 40, 'M', 'John', 'Mr', ''
1, 40, 'F', 'Betty', 'Mrs', ''
1, 40, 'F', 'Jane', 'Mrs', ''
Becomes
1, 40, 'M', 'Bob', 'Mr', '1'
1, 40, 'M', 'John', 'Mr', '2'
1, 40, 'F', 'Betty', 'Mrs', '1'
1, 40, 'F', 'Jane', 'Mrs', '2'
This will be a one time thing.....and the numbers should be in order from 1-? starting at the first record in each group that it finds. Thanx again !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply