Prev: Fix by Cloning table struction
Next: SQL Globalization / Localization
From: JimP on 28 Sep 2009 08:24
Is it possible to create a column in a view, sp, or table function, that
represents a running total?

e.g. I have an order number, order revision number and quantity ordered. I
may have a dozen revisions to an order, and would like to have a running
total of the quantity at each order revision number.


From: Plamen Ratchev on 28 Sep 2009 09:29
Here is one example:

CREATE VIEW RunningTotal
AS
SELECT order_nbr, revision_nbr, qty,
(SELECT SUM(B.qty)
FROM Orders AS B
WHERE B.order_nbr = A.order_nbr
AND B.revision_nbr <= A.revision_nbr) AS running_total
FROM Orders AS A;

--
Plamen Ratchev
http://www.SQLStudio.com
From: TheSQLGuru on 28 Sep 2009 10:35
Running totals are a very difficult task to do efficiently in the current
version of SQL Server's relational engine. Search sqlservercentral.com for
a wonderful thread on this topic with many code examples and lots of
testing. Itzik Ben-Gan recently did a series of articles in SQL Magazine
too.

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


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:ac2dnd7vxfajJ13XnZ2dnUVZ_vudnZ2d(a)speakeasy.net...
> Here is one example:
>
> CREATE VIEW RunningTotal
> AS
> SELECT order_nbr, revision_nbr, qty,
> (SELECT SUM(B.qty)
> FROM Orders AS B
> WHERE B.order_nbr = A.order_nbr
> AND B.revision_nbr <= A.revision_nbr) AS running_total
> FROM Orders AS A;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 | 
Pages: 1
Prev: Fix by Cloning table struction
Next: SQL Globalization / Localization