StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Well I decided to go down the iteration route using a mixture of joins and cursors. By JOINing the data table against itself I can create a link list of only those records that are consecutive. </p> <pre><code>INSERT INTO #CONSEC SELECT a.ID, a.Start, b.Finish, b.Amount FROM Data a JOIN Data b ON (a.Finish = b.Start) AND (a.ID = b.ID) </code></pre> <p>Then I can unwind the list by iterating over it with a cursor, and doing updates back to the data table to adjust (And delete the now extraneous records from the Data table)</p> <pre><code>DECLARE CCursor CURSOR FOR SELECT ID, Start, Finish, Amount FROM #CONSEC ORDER BY Start DESC @Total = 0 OPEN CCursor FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT WHILE @FETCH_STATUS = 0 BEGIN @Total = @Total + @Amount @Start_Last = @Start @Finish_Last = @Finish @ID_Last = @ID DELETE FROM Data WHERE Start = @Finish FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT IF (@ID_Last<> @ID) OR (@Finish<>@Start_Last) BEGIN UPDATE Data SET Amount = Amount + @Total WHERE Start = @Start_Last @Total = 0 END END CLOSE CCursor DEALLOCATE CCursor </code></pre> <p>This all works and has acceptable performance for typical data that I am using.</p> <p>I did find one small issue with the above code. Originally I was updating the Data table on each loop through the cursor. But this didn't work. It seems that you can only do one update on a record, and that multiple updates (in order to keep adding data) revert back to the reading the original contents of the record.</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