StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAggregate adjacent only records with T-SQL
text
Body
copied!<p>I have (simplified for the example) a table with the following data</p> <pre><code>Row Start Finish ID Amount --- --------- ---------- -- ------ 1 2008-10-01 2008-10-02 01 10 2 2008-10-02 2008-10-03 02 20 3 2008-10-03 2008-10-04 01 38 4 2008-10-04 2008-10-05 01 23 5 2008-10-05 2008-10-06 03 14 6 2008-10-06 2008-10-07 02 3 7 2008-10-07 2008-10-08 02 8 8 2008-10-08 2008-11-08 03 19 </code></pre> <p>The dates represent a period in time, the ID is the state a system was in during that period and the amount is a value related to that state.</p> <p>What I want to do is to aggregate the Amounts for <em>adjacent</em> rows with the <em>same</em> ID number, but keep the same overall sequence so that contiguous runs can be combined. Thus I want to end up with data like:</p> <pre><code>Row Start Finish ID Amount --- --------- ---------- -- ------ 1 2008-10-01 2008-10-02 01 10 2 2008-10-02 2008-10-03 02 20 3 2008-10-03 2008-10-05 01 61 4 2008-10-05 2008-10-06 03 14 5 2008-10-06 2008-10-08 02 11 6 2008-10-08 2008-11-08 03 19 </code></pre> <p>I am after a T-SQL solution that can be put into a SP, however I can't see how to do that with simple queries. I suspect that it may require iteration of some sort but I don't want to go down that path.</p> <p>The reason I want to do this aggregation is that the next step in the process is to do a SUM() and Count() grouped by the unique ID's that occur within the sequence, so that my final data will look something like:</p> <pre><code>ID Counts Total -- ------ ----- 01 2 71 02 2 31 03 2 33 </code></pre> <p>However if I do a simple </p> <pre><code>SELECT COUNT(ID), SUM(Amount) FROM data GROUP BY ID </code></pre> <p>On the original table I get something like</p> <pre><code>ID Counts Total -- ------ ----- 01 3 71 02 3 31 03 2 33 </code></pre> <p>Which is not what I want.</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