|
Home > Archive > MS SQL Server > May 2005 > Rolling totals in SQL
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Rolling totals in SQL
|
|
| Maury Markowitz 2005-05-24, 11:23 am |
| I need to run a rolling total for accounting purposes, finding where
inventory crosses zero (among other things). Here's what I have so far...
SELECT DISTINCT a_2.TradeDate AS TradeDate , a_2.sumofamount AS Amount,
Sum(a_1.sumofamount) AS RollingTotal
FROM (select TradeDate, sum(amount) as sumofamount from TRADES) AS a_1,
(select TradeDate, sum(amount) as sumofamount from TRADES) AS a_2
WHERE [a_1].[TradeDate])<=[a_2].[TradeDate]
GROUP BY a_2.TradeDate, a_2.sumofamount
order by a_2.TradeDate
So basically we group up all the buys and sells per day, and then add to
every line returned the total of everything before or on that day.
The problem is that as the number of trades grows the performance goes to
zero. This isn't unexpected, for every day after the start of time the number
of trades in the second list (before *this* date) grows larger and larger.
Can anyone suggest a better way to do this? Nothing seems obvious to me, but
I'm not much of an SQL guru.
Maury
| |
| Rick Sawtell 2005-05-24, 11:23 am |
|
"Maury Markowitz" < MauryMarkowitz@discu
ssions.microsoft.com> wrote in
message news:8A35D09C-5D36-4638-AFEF- C2D915592DA7@microso
ft.com...
> I need to run a rolling total for accounting purposes, finding where
> inventory crosses zero (among other things). Here's what I have so far...
>
> SELECT DISTINCT a_2.TradeDate AS TradeDate , a_2.sumofamount AS Amount,
> Sum(a_1.sumofamount) AS RollingTotal
> FROM (select TradeDate, sum(amount) as sumofamount from TRADES) AS a_1,
> (select TradeDate, sum(amount) as sumofamount from TRADES) AS a_2
> WHERE [a_1].[TradeDate])<=[a_2].[TradeDate]
> GROUP BY a_2.TradeDate, a_2.sumofamount
> order by a_2.TradeDate
>
> So basically we group up all the buys and sells per day, and then add to
> every line returned the total of everything before or on that day.
>
> The problem is that as the number of trades grows the performance goes to
> zero. This isn't unexpected, for every day after the start of time the
number
> of trades in the second list (before *this* date) grows larger and larger.
>
> Can anyone suggest a better way to do this? Nothing seems obvious to me,
but
> I'm not much of an SQL guru.
>
> Maury
Take a look at the ROLLLUP and CUBE operators in the BOL. This may do what
you need.
Rick Sawtell
MCT, MCSD, MCDBA
|
|
|
|
|