set item count to 0001 when only one donor/ increment by one when more

[brialex]

I need to update a table with item numbers for each record. For example if the donor only exists on the file one time, his item number would be 0001. however if the donor exists on file multiple times I need to set the first item number to 0001, the second to 0002, and so on. Any help would be much appreciated.

 

[brialex]

this is what I have so far

DECLARE @count int
DECLARE @pos int
DECLARE @total_subs int
DECLARE @N INT

SET @pos = 1

SET @total_subs = 228


SET @n = 1

SET @count = 0


-- Declare temp variables to store row data
DECLARE @v1 char(12)

-- Declare cursor selecting correspoding data with temp vars
DECLARE cur SCROLL CURSOR
FOR
SELECT dnr_nbr
FROM OPIxms_W
order by dnr_nbr

OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0 AND @pos<=@total_subs
BEGIN
SET @pos=@pos+@n
SET @count = @count + 1
FETCH ABSOLUTE @pos FROM cur INTO @v1
UPDATE OPIxms_W SET itm_nbr = @count WHERE dnr_nbr = @v1
END
CLOSE cur
DEALLOCATE cur



This works for marking each record with a unique number but I need the number to be 0001 for the first donor in a group(or the only donor in a group) and 0002 for the second donor in a group and so on.

 

[jbenson001]

What is your table structure? Had do you differentiate between doners, i.e. SSN, some primary key... some compostite key .. ???

 

[jbenson001]

Also, some sample data would help.

 

[brialex]

The donors each have a unique id assigned to them, such as
010000068070. If the same donor number appears twice you know that the donor has multiple gifts(donee's). The layout of my table is as follows: I need to populate the itm_nbr field with the record count as descibed.

select
ord_nbr as ord_nbr,
' ' as itm_nbr,
'N' as snt_flg,
dnr_nbr as dnr_nbr,
'X' as card_to,
' ' as gft_msg,
getdate() as dfr_dte,
' ' as dte_snt
into opixms_W
from cirsub_m sub
where key_cde in('GCPDM593','GGRDM593','GCPDM591','GGRDM591','GMEDM591','GFCDM591')
and left(ctg_id,2) = 'rf' and ctg_dte > '2005-08-15'

 

[brialex]

Here's some sample data. The first column is order number and last column is donor number. For the first record I would like the itm_nbr to be 0001. The itm_nbr for the second record should be 0001 and 0002 for the third record since record 2 and 3 have the same donor number and order number.

17436796 N 010000068002
17436821 N 010000068070
17436821 N 010000068070