Running Totals
-
I am trying to write a statement that will return the running totals of individual stocks in a portfolio given a start date and a number of days to look forward. The sum on each date should be the sum of all transactions that occured on or before that date. This is probably just a big 'duh' moment but I am having trouble coming up with the answer. Ideally, the results would be displayed with each date being a column. I was able to accomplish this with a loop and some dynamic sql, but it just seemed like there was a cleaner easier way. I would be happy with a result set that returned one row for each date for each ticker and then let the front end turn it on its side to show the desired format. Any ideas would be greatly appreciated.
CREATE TABLE TRX
(
TRADE_DATE SMALLDATETIME,
TICKER INT,
QUANTITY INT
)
GO
INSERT TRX VALUES ('10/01/05',1,500) --500
INSERT TRX VALUES ('10/02/05',1,-500) --0
INSERT TRX VALUES ('10/05/05',1,1500) --1500
INSERT TRX VALUES ('10/06/05',1,2000) --3500
INSERT TRX VALUES ('10/08/05',1,-3500) --0
INSERT TRX VALUES ('10/03/05',2,100) --100
INSERT TRX VALUES ('10/03/05',2,50) --150
INSERT TRX VALUES ('10/03/05',2,200) --350
INSERT TRX VALUES ('10/06/05',2,-100) --250
INSERT TRX VALUES ('10/07/05',2,-50) --200
INSERT TRX VALUES ('10/08/05',2,-100) --100
EXAMPLE 1
@startDate = '10/01/05'
@lookAhead = 3
RESULTS
TICKER VALUE_DATE QUANTITY
---------------------------------------
1 10/01/05 500
1 10/02/05 0
1 10/03/05 0
1 10/04/05 0
2 10/01/05 0
2 10/02/05 0
2 10/03/05 350
2 10/04/05 350
OR
TICKER 10/01/05 10/02/05 10/03/05 10/04/05
--------------------------------------------------
1 500 0 0 0
2 0 0 350 350
EXAMPLE 2
@startDate = '10/04/05'
@lookAhead = 4
RESULTS
TICKER VALUE_DATE QUANTITY
-----------------------------------
1 10/04/05 500
1 10/05/05 1500
1 10/06/05 3500
1 10/07/05 3500
1 10/08/05 0
2 10/04/05 350
2 10/05/05 350
2 10/06/05 250
2 10/07/05 200
2 10/08/05 100
OR
TICKER 10/04/05 10/05/05 10/06/05 10/07/05 10/08/05
-------------------------------------------------------------------------
1 0 1500 3500 3500 0
2 350 350 250 200 100
-
There was a highly unusual solution in Chapter 12 "Transact-SQL Examples and Brainteasers" of Ron Soukup and Kalen Delaney book "Inside Microsoft SQL Server 7". There is an edition for SQL Server 2000 too, I don't have it.
In the book there was a running_tot field and value filed that contains values you want to add.
Declare @run_total int
SELECT @run_total = 0
UPDATE MyTable
SET @run_total =running_tot=@run_total+value
From mytable
(I did not underline the previous line, it just happened)
Basically SET statement includes a variable equals field name equals same variable plus a value from another field. I have never seen 3 part syntax anywhere else but it worked for me.
Yelena
Regards,Yelena Varsha
-
EXAMPLE TO MY PREVOUS POST:
I added a field Total_qty as integer to Pubs.dbo.Sales and then run the following code:
Declare @run_total int
SELECT @run_total = 0
UPDATE sales
SET @run_total =total_qty=@run_total+qty
From sales
It updated 21 rows. Then I selected:
select qty, total_qty from sales
I am posting here only first lines of 21 rows that were returned:
qty total_qty
------ -----------
5 5
3 8
50 58
75 133
10 143
40 183
20 203
20 223
20 243
25 268
20 288
25 313
15 328
Regards,Yelena Varsha
-
I think this is what you want:
declare @startdate smalldatetime, @lookahead int
select @startDate = '20051001', @lookAhead = 3
-- select @startDate = '20051004', @lookAhead = 4
select m.trade_date
, m.ticker
, IsNull(t1.qty, 0) as quantity
, IsNull((select sum(t2.quantity)
from trx t2
where m.ticker = t2.ticker
and t2.trade_date <= m.trade_date),0) + IsNull(ini.qty,0) as RunningS
from (select t.ticker , t0.dates as trade_date
from
(select distinct ticker from trx) t
cross join
(select @startdate + number as dates
from master..spt_values
where number <= @lookahead and type ='P') t0
) m
Left join
(select trade_date, ticker, sum(quantity) qty
from trx
where trade_date between @startdate and Dateadd(day,@lookahead,@startdate)
group by trade_date, ticker ) t1
on m.trade_date = t1.trade_date and m.ticker = t1.ticker
Left join
(select trade_date, ticker, sum(quantity) qty
from trx
where trade_date < @startdate
group by trade_date, ticker ) ini
on m.trade_date = ini.trade_date and m.ticker = ini.ticker
order by m.ticker, m.trade_date
by the way on your second example the starting running total should be 0 not 500
Cheers,
* Noel -
While I humbly look at your code and try to figure out what exactly it does (besides produce the correct results) I would like to extend many thanks. I am going to read every one of your 2500+ posts too!
-
Sorry if it looks complicated, it really isn't let me disect it a bit so that is a bit more digestible
declare @startdate smalldatetime, @lookahead int
select @startDate = '20051001', @lookAhead = 3
-- select @startDate = '20051004', @lookAhead = 4
select m.trade_date -- taken from all possible combinations
, m.ticker -- taken from all possible combinations
, IsNull(t1.qty, 0) as quantity -- current qty for that date
-- The following is the result of the (starting value) + (running aggregation for the same ticker in the time span)
, IsNull((select sum(t2.quantity)
from trx t2
where m.ticker = t2.ticker
and t2.trade_date <= m.trade_date),0) -- running sum
+
IsNull(ini.qty,0) -- starting value
as RunningS
from
(
--This query returns all the possible combinations of Dates and Ticker numbers for the period you want
select t.ticker , t0.dates as trade_date
from
(select distinct ticker from trx) t -- These are all the possible Ticker Numbers
cross join
(select @startdate + number as dates
from master..spt_values -- This is just simulating a numbers table
where number <= @lookahead and type ='P' -- (something very handy to have on every database)
) t0
) m
Left join
(select trade_date, ticker, sum(quantity) qty
from trx
where trade_date between @startdate and Dateadd(day,@lookahead,@startdate)
group by trade_date, ticker
) t1 -- With this we are eliminating duplicated dates by aggregation
-- also we are limiting the timespan to improve in performance
on m.trade_date = t1.trade_date and m.ticker = t1.ticker
Left join
(select trade_date, ticker, sum(quantity) qty
from trx
where trade_date < @startdate
group by trade_date, ticker
) ini --- With this we are calculating the value at
--- startup as the agregation of the all previous dates
on m.trade_date = ini.trade_date and m.ticker = ini.ticker
order by m.ticker, m.trade_date
Hope this helps
* Noel
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply