Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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!
0
pauldonson
Asked:
pauldonson
  • 2
1 Solution
 
dbbishopCommented:
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
0
 
dbbishopCommented:
Sorry, the UPDATE statement should be:

    UPDATE @Export
    SET Payment = Payment + ' ' + CAST(@Inc AS VARCHAR(2))
    WHERE ID = @ID
0
 
pauldonsonAuthor Commented:
Marvellous! Thank you very much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now