TSQL : Incrementing a column value based on another column's value (SQL Server)

Question

I have a legacy stored procedure that I'm trying to modify.

This is a dump of the data that's in the table that's being modified.

Each time the PayrollRateID (2nd column, example values shown here 25, 27, 28 etc) increments the 'Incrementer' column needs incrementing.

The ID column is generated using Row_Number()

Whoever wrote the stored procedure is using a column to go down each row in the table, and set the value of the incrementer column each time the PayrollRateID value cahnges. This, unfortunately, is taking a couple of minutes to do 20,000 odd rows. That's just a few days data, if I run with a month's worth, well, you can imagine the performance issue I'm getting.

The logic of the sproc is to start off with an incrementer value of 0, and with the first PayrollRateID (in the example - 25), give all those 0's, when the PayrollRateID changes, then increment the count in the incrementer column, and give all the next value (in the example - 27's) a 1, when it changes to the next value (in my example 28) then give the incrementer column a 2, etc.

Date
                        PayrollRateID
                            DayNum  
                                Variance
                                        VarianceID
                                            Incrementer
                                                ID
2011-07-25 00:00:00.000 25  1   1.00    0   0   1
2011-07-26 00:00:00.000 25  2   1.00    0   0   2
2011-07-27 00:00:00.000 25  3   1.00    0   0   3
2011-07-28 00:00:00.000 25  4   1.00    0   0   4
2011-07-29 00:00:00.000 25  5   1.00    0   0   5
2011-07-30 00:00:00.000 25  6   1.00    0   0   6
2011-07-31 00:00:00.000 25  7   1.00    0   0   7
2011-08-01 00:00:00.000 25  1   1.00    0   0   8
2011-08-02 00:00:00.000 25  2   1.00    0   0   9
2011-08-03 00:00:00.000 25  3   1.00    0   0   10
2011-08-04 00:00:00.000 25  4   1.00    0   0   11
2011-08-05 00:00:00.000 25  5   1.00    0   0   12
2011-08-06 00:00:00.000 25  6   1.00    0   0   13
2011-08-07 00:00:00.000 25  7   1.00    0   0   14
2011-08-08 00:00:00.000 25  1   1.00    0   0   15
2011-08-09 00:00:00.000 25  2   1.00    0   0   16
2011-08-10 00:00:00.000 25  3   1.00    0   0   17
2011-08-11 00:00:00.000 25  4   1.00    0   0   18
2011-08-12 00:00:00.000 25  5   1.00    0   0   19
2011-08-13 00:00:00.000 25  6   1.00    0   0   20
2011-08-14 00:00:00.000 25  7   1.00    0   0   21
2011-07-25 00:00:00.000 27  1   1.00    0   1   22
2011-07-26 00:00:00.000 27  2   1.00    0   1   23
2011-07-27 00:00:00.000 27  3   1.00    0   1   24
2011-07-28 00:00:00.000 27  4   1.00    0   1   25
2011-07-29 00:00:00.000 27  5   1.00    0   1   26
2011-07-30 00:00:00.000 27  6   1.00    0   1   27
2011-07-31 00:00:00.000 27  7   1.00    0   1   28
2011-08-01 00:00:00.000 27  1   1.00    0   1   29
2011-08-02 00:00:00.000 27  2   1.00    0   1   30
2011-08-03 00:00:00.000 27  3   1.00    0   1   31
2011-08-04 00:00:00.000 27  4   1.00    0   1   32
2011-08-05 00:00:00.000 27  5   1.00    0   1   33
2011-08-06 00:00:00.000 27  6   1.00    0   1   34
2011-08-07 00:00:00.000 27  7   1.00    0   1   35
2011-08-08 00:00:00.000 27  1   1.00    0   1   36
2011-08-09 00:00:00.000 27  2   1.00    0   1   37
2011-08-10 00:00:00.000 27  3   1.00    0   1   38
2011-08-11 00:00:00.000 27  4   1.00    0   1   39
2011-08-12 00:00:00.000 27  5   1.00    0   1   40
2011-08-13 00:00:00.000 27  6   1.00    0   1   41
2011-08-14 00:00:00.000 27  7   1.00    0   1   42
2011-07-25 00:00:00.000 28  1   1.00    0   2   43
2011-07-26 00:00:00.000 28  2   1.00    0   2   44
2011-07-27 00:00:00.000 28  3   1.00    0   2   45
2011-07-28 00:00:00.000 28  4   1.00    0   2   46
2011-07-29 00:00:00.000 28  5   1.00    0   2   47
2011-07-30 00:00:00.000 28  6   1.00    0   2   48
2011-07-31 00:00:00.000 28  7   1.00    0   2   49
2011-08-01 00:00:00.000 28  1   1.00    0   2   50
2011-08-02 00:00:00.000 28  2   1.00    0   2   51
2011-08-03 00:00:00.000 28  3   1.00    0   2   52
2011-08-04 00:00:00.000 28  4   1.00    0   2   53
2011-08-05 00:00:00.000 28  5   1.00    0   2   54
2011-08-06 00:00:00.000 28  6   1.00    0   2   55
2011-08-07 00:00:00.000 28  7   1.00    0   2   56
2011-08-08 00:00:00.000 28  1   1.00    0   2   57
2011-08-09 00:00:00.000 28  2   1.00    0   2   58
2011-08-10 00:00:00.000 28  3   1.00    0   2   59
2011-08-11 00:00:00.000 28  4   1.00    0   2   60
2011-08-12 00:00:00.000 28  5   1.00    0   2   61
2011-08-13 00:00:00.000 28  6   1.00    0   2   62
2011-08-14 00:00:00.000 28  7   1.00    0   2   63
2011-07-25 00:00:00.000 34  1   1.00    0   3   64
2011-07-26 00:00:00.000 34  2   1.00    0   3   65
2011-07-27 00:00:00.000 34  3   1.00    0   3   66
2011-07-28 00:00:00.000 34  4   1.00    0   3   67
2011-07-29 00:00:00.000 34  5   1.00    0   3   68
2011-07-30 00:00:00.000 34  6   1.00    0   3   69
2011-07-31 00:00:00.000 34  7   1.00    0   3   70
2011-08-01 00:00:00.000 34  1   1.00    0   3   71
2011-08-02 00:00:00.000 34  2   1.00    0   3   72
2011-08-03 00:00:00.000 34  3   1.00    0   3   73
2011-08-04 00:00:00.000 34  4   1.00    0   3   74
2011-08-05 00:00:00.000 34  5   1.00    0   3   75
2011-08-06 00:00:00.000 34  6   1.00    0   3   76
2011-08-07 00:00:00.000 34  7   1.00    0   3   77
2011-08-08 00:00:00.000 34  1   1.00    0   3   78
2011-08-09 00:00:00.000 34  2   1.00    0   3   79
2011-08-10 00:00:00.000 34  3   1.00    0   3   80
2011-08-11 00:00:00.000 34  4   1.00    0   3   81
2011-08-12 00:00:00.000 34  5   1.00    0   3   82
2011-08-13 00:00:00.000 34  6   1.00    0   3   83
2011-08-14 00:00:00.000 34  7   1.00    0   3   84
2011-07-25 00:00:00.000 38  1   1.00    0   4   85
2011-07-26 00:00:00.000 38  2   1.00    0   4   86
2011-07-27 00:00:00.000 38  3   1.00    0   4   87
2011-07-28 00:00:00.000 38  4   1.00    0   4   88
2011-07-29 00:00:00.000 38  5   1.00    0   4   89
2011-07-30 00:00:00.000 38  6   1.00    0   4   90
2011-07-31 00:00:00.000 38  7   1.00    0   4   91
2011-08-01 00:00:00.000 38  1   1.00    0   4   92
2011-08-02 00:00:00.000 38  2   1.00    0   4   93
2011-08-03 00:00:00.000 38  3   1.00    0   4   94
2011-08-04 00:00:00.000 38  4   1.00    0   4   95
2011-08-05 00:00:00.000 38  5   1.00    0   4   96
2011-08-06 00:00:00.000 38  6   1.00    0   4   97
2011-08-07 00:00:00.000 38  7   1.00    0   4   98
2011-08-08 00:00:00.000 38  1   1.00    0   4   99
2011-08-09 00:00:00.000 38  2   1.00    0   4   100
2011-08-10 00:00:00.000 38  3   1.00    0   4   101
2011-08-11 00:00:00.000 38  4   1.00    0   4   102
2011-08-12 00:00:00.000 38  5   1.00    0   4   103
2011-08-13 00:00:00.000 38  6   1.00    0   4   104
2011-08-14 00:00:00.000 38  7   1.00    0   4   105
2011-07-25 00:00:00.000 40  1   1.00    0   5   106
2011-07-26 00:00:00.000 40  2   1.00    0   5   107
2011-07-27 00:00:00.000 40  3   1.00    0   5   108
2011-07-28 00:00:00.000 40  4   1.00    0   5   109
2011-07-29 00:00:00.000 40  5   1.00    0   5   110
2011-07-30 00:00:00.000 40  6   1.00    0   5   111
2011-07-31 00:00:00.000 40  7   1.00    0   5   112
2011-08-01 00:00:00.000 40  1   1.00    0   5   113
2011-08-02 00:00:00.000 40  2   1.00    0   5   114
2011-08-03 00:00:00.000 40  3   1.00    0   5   115
2011-08-04 00:00:00.000 40  4   1.00    0   5   116
2011-08-05 00:00:00.000 40  5   1.00    0   5   117
2011-08-06 00:00:00.000 40  6   1.00    0   5   118
2011-08-07 00:00:00.000 40  7   1.00    0   5   119
2011-08-08 00:00:00.000 40  1   1.00    0   5   120
2011-08-09 00:00:00.000 40  2   1.00    0   5   121
2011-08-10 00:00:00.000 40  3   1.00    0   5   122
2011-08-11 00:00:00.000 40  4   1.00    0   5   123
2011-08-12 00:00:00.000 40  5   1.00    0   5   124
2011-08-13 00:00:00.000 40  6   1.00    0   5   125
2011-08-14 00:00:00.000 40  7   1.00    0   5   126
2011-07-25 00:00:00.000 41  1   1.00    0   6   127
2011-07-26 00:00:00.000 41  2   1.00    0   6   128
2011-07-27 00:00:00.000 41  3   1.00    0   6   129
2011-07-28 00:00:00.000 41  4   1.00    0   6   130
2011-07-29 00:00:00.000 41  5   1.00    0   6   131
2011-07-30 00:00:00.000 41  6   1.00    0   6   132
2011-07-31 00:00:00.000 41  7   1.00    0   6   133
2011-08-01 00:00:00.000 41  1   1.00    0   6   134
2011-08-02 00:00:00.000 41  2   1.00    0   6   135
2011-08-03 00:00:00.000 41  3   1.00    0   6   136
2011-08-04 00:00:00.000 41  4   1.00    0   6   137
2011-08-05 00:00:00.000 41  5   1.00    0   6   138
2011-08-06 00:00:00.000 41  6   1.00    0   6   139
2011-08-07 00:00:00.000 41  7   1.00    0   6   140
2011-08-08 00:00:00.000 41  1   1.00    0   6   141
2011-08-09 00:00:00.000 41  2   1.00    0   6   142
2011-08-10 00:00:00.000 41  3   1.00    0   6   143
2011-08-11 00:00:00.000 41  4   1.00    0   6   144
2011-08-12 00:00:00.000 41  5   1.00    0   6   145
2011-08-13 00:00:00.000 41  6   1.00    0   6   146
2011-08-14 00:00:00.000 41  7   1.00    0   6   147
2011-07-25 00:00:00.000 42  1   1.00    0   7   148
2011-07-26 00:00:00.000 42  2   1.00    0   7   149
2011-07-27 00:00:00.000 42  3   1.00    0   7   150
2011-07-28 00:00:00.000 42  4   1.00    0   7   151
2011-07-29 00:00:00.000 42  5   1.00    0   7   152
2011-07-30 00:00:00.000 42  6   1.00    0   7   153
2011-07-31 00:00:00.000 42  7   1.00    0   7   154
2011-08-01 00:00:00.000 42  1   1.00    0   7   155
2011-08-02 00:00:00.000 42  2   1.00    0   7   156
2011-08-03 00:00:00.000 42  3   1.00    0   7   157
2011-08-04 00:00:00.000 42  4   1.00    0   7   158
2011-08-05 00:00:00.000 42  5   1.00    0   7   159
2011-08-06 00:00:00.000 42  6   1.00    0   7   160
2011-08-07 00:00:00.000 42  7   1.00    0   7   161
2011-08-08 00:00:00.000 42  1   1.00    0   7   162
2011-08-09 00:00:00.000 42  2   1.00    0   7   163
2011-08-10 00:00:00.000 42  3   1.00    0   7   164
2011-08-11 00:00:00.000 42  4   1.00    0   7   165
2011-08-12 00:00:00.000 42  5   1.00    0   7   166
2011-08-13 00:00:00.000 42  6   1.00    0   7   167
2011-08-14 00:00:00.000 42  7   1.00    0   7   168
2011-07-25 00:00:00.000 44  1   1.00    0   8   169
2011-07-26 00:00:00.000 44  2   1.00    0   8   170
2011-07-27 00:00:00.000 44  3   1.00    0   8   171
2011-07-28 00:00:00.000 44  4   1.00    0   8   172
2011-07-29 00:00:00.000 44  5   1.00    0   8   173

I'm going to try and group on PayrollRateID, and use Row_Number() to generate the value for the Incrementer column.

Any better suggestions would be very much appreciated.

SQL Server 2008.

codingbadger · Accepted Answer · 2012-06-19 11:28:14Z

3

What about this:

Update a
Set Incrementer = b.Incrementer
From dbo.YourTable a
Join
(
Select PayRollId,
       Row_Number() Over(Order By PayRollId) as [Incrementer]
From dbo.YourTable 
Group By PayRollId
)b on a.PayRollId = b.PayRollId

edited Jun 19, 2012 at 11:28

answered Jun 19, 2012 at 10:46

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cometbill Over a year ago

I couldn't get this to run in its entirety, but the inner select gives the same effect that I got earlier, in that the incrementer would increment for each PayrollRateID, and then reset for the next. The effect I'm after, is that all PayrollRateID's get the same value, and the incrementer gives a different value for each PayrollRateID

codingbadger Over a year ago

@cometbill: Updated my answer, I think this will do it for you.

cometbill Over a year ago

Take the a. off the a.Incrementer, on the second line, and it works fine. Otherwise I get a 'The multi-part identifier "a.Incrementer" could not be bound.' error.

Peter Over a year ago

Good answer. You can also use DENSE_RANK - I think this would have been easier.

SELECT [Date], PayrollRateID, DayNum, Variance, DENSE_RANK() OVER (ORDER BY PayrollRateID) AS VarianceID, IncrementerID FROM YourTable

Kane · Accepted Answer · 2012-06-19 10:39:42Z

Not sure if I understand the logic in the stored proc, but, could you create another table say [PayrollIncrementer] that has an ID identity seed value and inserting into the table when you need to increment the ID.

CREATE TABLE [dbo].[PayrollIncrementer]
(
    [Id] INT NOT NULL IDENTITY(1),
    [DateAdded] DATETIME NOT NULL
)

Your stored proc would use this table something like this

BEGIN
    -- PROC BEGIN

    DECLARE @id INT
    INSERT INTO [dbo].[PayrollIncrementer] ([DateAdded]) VALUES (GETDATE())
    SET @id = SCOPE_IDENTITY()

    -- remainder of proc & use the identity

    -- PROC END
END

Sorry if you have already thought of this, but I don't really understand the expected outcome.

Jon Lin · Accepted Answer · 2012-06-19 21:00:54Z

0

How about just using DENSE_RANK instead of ROW_NUMBER? That will do everything at once.

edited Jun 19, 2012 at 21:00

Jon Lin

144k29 gold badges226 silver badges224 bronze badges

answered Jun 19, 2012 at 19:30

ToineR

1

Collectives™ on Stack Overflow

TSQL : Incrementing a column value based on another column's value (SQL Server)

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related