asked on

Updating field data based on its position within a group

Hi,

I have an SQL function that creates a table @Export with the columns Employee and Payment which would contain data such as:

Employee Payment
1070 Standard
1070 Overtime
1419 Standard
1419 Standard
1663 Standard
1663 Overtime
1663 Overtime

Can anyone think of an efficient way of updating this table to read:
1070 Standard 1
1070 Overtime 1
1419 Standard 1
1419 Standard 2
1663 Standard 1
1663 Overtime 1
1663 Overtime 2

So, In other words for each employee it is numbering each different payment starting at 1 each time for each payment type.

Thanks!

D B

You will need to add an identity column to your @export table (e.g. ID INT IDENTITY(1,1)...)
Your data will then look like this:
ID Employee Payment
1 1070 Standard
2 1070 Overtime
3 1419 Standard
4 1419 Standard
5 1663 Standard
6 1663 Overtime
7 1663 Overtime

Then, use the following:
DECLARE @Inc INT, @ID INT, @Employee INT, @Payment VARCHAR(50), @PrvEmployee INT, @PrvPayment VARCHAR(50)

DECLARE myCursor CURSOR FAST_FORWARD FOR
SELECT ID, Employee, Payment FROM @Export
OPEN myCursor
SET @Inc = 1
SET @Employee = 0
SET @PrvEmployee = 0
SET @Payment = SPACE(0)
SET @PrvPayment = SPACE(0)

FETCH myCursor INTO @ID, @Employee, @Payment
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Employee = @PrvEmployee) AND (@Payment = @PrvPayment)
SET @Inc = @Inc + 1
ELSE
BEGIN
SET @Inc = 1
SET @PrvEmployee = @Employee
SET @PrvPayment = @Payment
END
UPDATE @Export
SET @Payment = Payment + ' ' + CAST(@Inc AS VARCHAR(2))
WHERE ID = @ID
FETCH myCursor INTO @ID, @Employee, @Payment
END

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

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

Start Free Trial

pauldonson

ASKER

Marvellous! Thank you very much.