StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTSQL : Incrementing a column value based on another column's value (SQL Server)
text
Body
copied!<p>I have a legacy stored procedure that I'm trying to modify.</p> <p>This is a dump of the data that's in the table that's being modified.</p> <p>Each time the PayrollRateID (2nd column, example values shown here 25, 27, 28 etc) increments the 'Incrementer' column needs incrementing.</p> <p>The ID column is generated using Row_Number()</p> <p>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.</p> <p>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.</p> <pre><code>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 </code></pre> <p>I'm going to try and group on PayrollRateID, and use Row_Number() to generate the value for the Incrementer column.</p> <p>Any better suggestions would be very much appreciated.</p> <p>SQL Server 2008.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload