Efficient running totals

It isn't 'fairly' inefficient - it is extremely inefficient! :-)

See here for a huge and very informative thread on running totals
calculations for sql server:

http://www.sqlservercentral.com/arti...uerying/61716/
and the related discussion thread:
http://www.sqlservercentral.com/Foru...802-203-1.aspx

There were 211 posts in this thread! People did an amazing amount of coding
and benchmarking too to prove/disprove method performance metrics.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Maury Markowitz" wrote in message
news:47dacb1d-4ad8-4cc1-aaec-1dfb8807b88d@d45g2000hsc.googlegroups.com...
>I have a 3 million+ row table with every inventory change across a
> number of "locations". I can easily generate my total remaining
> inventory for any item by summing up over all of these trades and
> grouping by the item id...
>
> SELECT itemId, SUM(quantity) FROM tblTrades GROUP BY itemId HAVING
> ABS(SUM(quantity)) > 0.1
>
> However what I would like to do now is report not on today's numbers,
> but also show the changes in those numbers over time. For instance, if
> widgets were purchased twice and sold twice:
>
> Dec-1 purchased 100 widgets
> Dec-15 sold 50 widgets
> Jan-5 purchased 75 widgets
> Jan-15 sold 125 widgets
>
> I would like to end up with a report that shows four lines, like
> this....
>
>
> Dec-1 +100 widgets leaving 100
> Dec-15 -50 widgets leaving 50
> Jan-5 +75 widgets leaving 125
> Jan-15 -125 widgets leaving 0
>
> I do this now by using a derived table "inner" selecting the same data
> but WHERE inner.date > everything that happened before this line. This works, but is fairly
> inefficient, because as the time span grows the number of these
> derived tables that has to be calculated (one for each event) grows
> exponentially.
>
> Am I missing some obvious efficient way to do this within SQL without
> abusing it so much?
>
> Maury

Maury Markowitz (maury.markowitz@gmail.com) writes:
> I do this now by using a derived table "inner" selecting the same data
> but WHERE inner.date > everything that happened before this line. This works, but is fairly
> inefficient, because as the time span grows the number of these
> derived tables that has to be calculated (one for each event) grows
> exponentially.
>
> Am I missing some obvious efficient way to do this within SQL without
> abusing it so much?

Yes, as Joe pointed out, you are missing a feature in SQL Server. We are
all missing a sorely needed feature in SQL Server. You can vote for it here:

https://connect.microsoft.com/SQLSer...dbackID=254387


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 18, 5:18*pm, "TheSQLGuru" wrote:
> http://www.sqlservercentral.com/arti...uerying/61716/
> and the related discussion thread:http://www.sqlservercentral.com/Foru...802-203-1.aspx

Is there a way of reading them without giving out my e-mail?

Maury

>> But I do find the syntax a little opaque, what does the OVER mean in this example? <<

You really need to come to one of my talks and see the power points
for this :)

OVER() hold three subclauses.
PARTITION BY says take the set of values in the " function>()" at the font and make partitions (think sub-tables) from
them.
ORDER BY says sort each sub-table
ROW says put a window or frame as described over the
rows in the partition.

Finally, apply the "()" on the subset inside the
window and return a scalar value.

Google around for more details.

Using a fake email perhaps? Go gen up a hotmail, etc acct and use that.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Maury Markowitz" wrote in message
news:d3557ced-e600-444e-94d4-ac3041d21167@a70g2000hsh.googlegroups.com...
On Mar 18, 5:18 pm, "TheSQLGuru" wrote:
> http://www.sqlservercentral.com/arti...uerying/61716/
> and the related discussion
> thread:http://www.sqlservercentral.com/Foru...802-203-1.aspx

Is there a way of reading them without giving out my e-mail?

Maury