Running value based on previous row
-
I have an interesting case about moving value.
I really hope that you guys can help me to solve it.
Supposed I have a table as this script :
CREATE TABLE TData (
DateOnData datetime,
Item varchar(10)
ClosingPrice float
)
The table has this following data :
DateOnData,Item,ClosingPrice
1-Jan-01,sugar,400
1-Jan-01,salt,450
1-Jan-01,coffee,700
2-Jan-01,sugar,425
2-Jan-01,salt,425
2-Jan-01,coffee,695
3-Jan-01,sugar,410
3-Jan-01,salt,435
3-Jan-01,coffee,675
4-Jan-01,sugar,415
4-Jan-01,salt,420
4-Jan-01,coffee,725
I want to produce the following output :
DateOnData,Item,ClosingPrice,MovingValue
1-Jan-01,sugar,400,400
2-Jan-01,sugar,425,405
3-Jan-01,sugar,410,406
4-Jan-01,sugar,415,407.8
1-Jan-01,salt,450,450
2-Jan-01,salt,425,445
3-Jan-01,salt,435,443
4-Jan-01,salt,420,438.4
1-Jan-01,coffee,700,700
2-Jan-01,coffee,695,699
3-Jan-01,coffee,675,694.2
4-Jan-01,coffee,725,700.36
The calculation of MovingValue column is :
- For the first row (date) of certain item, MovingValue = ClosingPrice
- For other row of certain item,
MovingValue = (MovingValue of previous date) plus 0.2 * (ClosingPrice of row - MovingValue of previous date)
For example :
On date 2-Jan-01 on item sugar,
MovingValue = 400 plus 0.2 * (425 - 400) = 405
On date 3-Jan-01 on item sugar,
MovingValue = 405 plus 0.2 * (410 - 405) = 406
Thanks in advance, and thousands thanks for your help ...
Anyway, why does the plus sign not appear on screen ???? 🙂
-
Not sure about the plus sign, might be protection against SQL Injection.
Anyway, you need to self join back to the existing table to get the previous row. Now the previous row will only be based on some thing like the date in your case, not on physical order.
so you could
select a.price * .2 + ( b.price - a.price)
from TData A
inner join TData B
on a.Item = b.item
and a.DateonData = (select max(DateonData)
from TData C
where c.Item = a.item
and c.DateonData < b.DateonData
)
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
You can use an update statement to achieve this... but Steve's way may be better. Have to test it i reckon.
You need a clustered index to use the update statement to do it... if your clustered index on the table isn't/shouldn't be in the order of the rows you'll be updating you'd have to cluster it appropriately in a temp table to get it to work.
In the data I work on i'm updating a smallint field - it takes ~3 seconds to update ~5 million rows, ~8 seconds for ~11 million rows. The set statements have a huge amount of logic in them - seems to have low overhead.
If I add a where clause on the update it can slows it down dramatically - perhaps avoidable with proper indexing. In my case I am working on single-user tables and output only the resulting data for multi-user access so I haven't had to use the where clause I just tried it to see if it'd speed me up any.
-
Problem with Steve's answer is it doesn't work.
What's being asked is simple in Excel, but not as easy in SQL Server - calculate each row based on a calculation in the previous row. I don't know of an easy way to do this in SQL Server, If anyone does, please let me know.
However, it is very solvable, via a loop or a clever trick. First I try the clever trick - summation (flashback to Calculus 2 class):
MovingValue(Date) = {ClosingPrice(1) * 4 + ClosingPrice(2) * 1 + ClosingPrice(2) * 1.25 + ClosingPrice(2) * 1.25^2...} / 4 + 1 + 1.25 + 1.25^2 ...
So create a table with a "multiplier" column, first row 4, then 1.25 ^0, 1.25^1, 1.25^2, ..., then multiply each row with the corresponding row in TData (order by date for each item) and divide by the multiplier sum to that row.
I'll stop here, because the method should work, but SQLServer is not so good at math. Try it in Excel.
A longer, but easier method uses a while loop (cursor would also work about the same). I write the following for the data as shown. You will probably want to modify it for your real-life situation, maybe for a trigger or procedure. If TData has a lot of rows, you would probably only want to update the MovingValue when data changes. Running this whenever data is requested would be slow.
--add moving value column
alter table TData add MovingValue float
--add CountDays column for clarity
alter table TData add CountDays int
go -- For QA
update tdata
set CountDays = (select datediff( d , min(a.DateonData),tdata.DateonData) from tData a where a.Item = tdata.Item)
declare @C int
declare @cEnd int
select @cEnd = max(CountDays) from tdata
set @C = 0
update tdata
set MovingValue = ClosingPrice where CountDays = 0
while @C < @cEnd
BEGIN
--set MovingValue
update tdata
set MovingValue =
(select .8 * t.MovingValue from tdata t
where t.CountDays = tdata.CountDays - 1 and t.Item= tdata.Item)
+ .2 * ClosingPrice
where tdata.CountDays = @C
END
--See results:
select * from tdata order by item desc,dateondata
That should do it. Please let me know how it works out. How is it in Indonesia?
Edited by - stubob on 12/09/2003 10:39:07 PM
Edited by - stubob on 12/10/2003 11:22:30 AM
Data: Easy to spill, hard to clean up!
-
Use function is a way.
Here is the test case.
Change Price to money for accurate result.
create table tdata(DateOnData datetime, Item varchar(10), ClosingPrice money)
insert tdata values('1-Jan-01','sugar',400)
insert tdata values('1-Jan-01','salt',450)
insert tdata values('1-Jan-01','coffee',700)
insert tdata values('2-Jan-01','sugar',425)
insert tdata values('2-Jan-01','salt',425)
insert tdata values('2-Jan-01','coffee',695)
insert tdata values('3-Jan-01','sugar',410)
insert tdata values('3-Jan-01','salt',435)
insert tdata values('3-Jan-01','coffee',675)
insert tdata values('4-Jan-01','sugar',415)
insert tdata values('4-Jan-01','salt',420)
insert tdata values('4-Jan-01','coffee',725)
go
select * from tdata order by 1
go
create function fn_movingvalue (@DateOnData datetime, @Item varchar(10))
returns money
as
begin
declare @ren money -- return value
declare @base1 money, @base2 money, @maxn int
declare @Tmp table (closingprice money, n int identity(0,1))
select @base1 = 0.8, @base2 = 0.2
-- closeprice, n
insert @Tmp (closingprice)
select closingprice from tdata where item = @item and DateOnData <= @DateOnData
order by DateOnData desc
select @maxn = @@identity
select @ren = sum(
case when n = @maxn then power(@base1,n)*ClosingPrice
else @base2*power(@base1,n)*ClosingPrice
end)
from @Tmp
return @ren
end
go
select DateOnData, Item, ClosingPrice, movingvalue=dbo.fn_movingvalue(DateOnData, Item)
from tdata
order by 2,1
go
drop table tdata
drop function fn_movingvalue
go
-
For a great discussion of your options, that can be done without iterating through each row, look at:
-
Hmmm ... couldn't find the right answer using SQL. Perhaps there is , but it is difficult. I guess I'll use looping anyway ... ;). Thanks guys ...
-
Is this what you are after ?
CREATE TABLE TDATA(DATEONDATA DATETIME, ITEM VARCHAR(10), CLOSINGPRICE MONEY, MOVINGVALUE MONEY)
INSERT TDATA VALUES('1-JAN-01','SUGAR',400,400)
INSERT TDATA VALUES('1-JAN-01','SALT',450,450)
INSERT TDATA VALUES('1-JAN-01','COFFEE',700,700)
INSERT TDATA VALUES('2-JAN-01','SUGAR',425,405)
INSERT TDATA VALUES('2-JAN-01','SALT',425,445)
INSERT TDATA VALUES('2-JAN-01','COFFEE',695,699)
INSERT TDATA VALUES('3-JAN-01','SUGAR',410,406)
INSERT TDATA VALUES('3-JAN-01','SALT',435,443)
INSERT TDATA VALUES('3-JAN-01','COFFEE',675,694.2)
INSERT TDATA VALUES('4-JAN-01','SUGAR',415,407.8)
INSERT TDATA VALUES('4-JAN-01','SALT',420,438.4)
INSERT TDATA VALUES('4-JAN-01','COFFEE',725,700.36)
SELECT
A.DATEONDATA AS PREVDATE
,A.ITEM
,A.CLOSINGPRICE AS PREVCLOSING
,B.DATEONDATA AS CURRDATE
,B.CLOSINGPRICE AS CURRCLOSING
,(A.MOVINGVALUE ) + 0.2 * (B.CLOSINGPRICE - A.MOVINGVALUE) AS CURRMOVING
FROM
TDATA A
JOIN
TDATA B
ON A.ITEM=B.ITEM
AND DATEDIFF(DAY,A.DATEONDATA,B.DATEONDATA)=1
just make sure you don't allow duplicates on Date AND Item
HTH
* Noel -
quote:
For a great discussion of your options, that can be done without iterating through each row, look at:http://www.sqlteam.com/item.asp?ItemID=3856
That url doesn't mention the in-line update.
I ran the fastest solution on that page on a a closed system (me the only user, nothing else going on) and the fastest of 3 runs was 1153 ms. The solution below had a slowest run time of 673 ms. On a large dataset the difference would be far more significant.
As of yet, I haven't found a better solution for spreadsheet-like manipulation.
alter table Sales add RunningTotal money not null default 0
go
print 'starting solution 4'
declare @rt money, @Start datetime
set @rt = 0
set @Start = getdate()
update Sales set
@rt = RunningTotal = @rt + Sales
select * from Sales
print 'Solution 4 exec time:'+str(datediff(ms,@Start,getdate()))
-
Can you insert the running total value at INSERT time?
But if you update the ClosingDate Values
an INDEXED VIEW with the previous query may do the Trick
as allways ... test, test, test
Edited by - noeld on 12/11/2003 09:59:17 AM
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply