|
Subject:
|
updating running changes
|
|
Posted By:
|
cs8271506
|
Post Date:
|
12/30/2005 3:43:21 PM
|
How can I update a column in row n+1 with the running changes from row n to row n+1?
For example, I have the following table:
SubID Date Price Price Change
1 2/1/2004 10 Null
1 2/8/2004 13 3
1 2/15/2004 12 -1
2 2/1/2004 11 Null
2 2/8/2004 15 4
This is a fact table in a data warehouse of week-by-week subscriptions, and we want to report on the price changes by subscriber.
My method so far has been to put the table into a temp table, join the two tables by ID and (Date - 1 week), and update [Price Change] by the difference between the two Prices. It works, but I'm thinking there might be a better way.
tia,
Conrad
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/10/2006 11:49:58 PM
|
--Try this as a better way
--Setup test data
CREATE TABLE dbo.Subscription
(SUBID int
,sDate DATETIME
,Price int
,PriceChange int
)
INSERT dbo.Subscription VALUES (1, '2/1/2004', 10, NULL)
INSERT dbo.Subscription VALUES (1, '2/8/2004', 13, NULL)
INSERT dbo.Subscription VALUES (1, '2/15/2004', 12, NULL)
INSERT dbo.Subscription VALUES (2, '2/1/2004', 11, NULL)
INSERT dbo.Subscription VALUES (2, '2/8/2004', 15, NULL)
SELECT S.SUBID, S.SDate, PriorWk.SDate, S.Price, PriorWk.PRice, S.Price - PriorWk.PRice
FROM dbo.Subscription S
JOIN dbo.Subscription PriorWk
ON S.SUBID = PriorWk.SUBID
AND S.sDate = DATEADD(dd,7,PriorWk.sDate)
UPDATE dbo.Subscription
SET PriceChange = Subscription.Price - PriorWk.PRice
FROM dbo.Subscription -- Not using alias to avoid Msg 8154 table name is ambiguous
JOIN dbo.Subscription as PriorWk
ON Subscription.SUBID = PriorWk.SUBID
AND Subscription.sDate = DATEADD(dd,7,PriorWk.sDate)
SELECT * FROM dbo.Subscription
/* Output
SUBID sDate Price PriceChange
----------- ------------------------------------------------------ ----------- -----------
1 2004-02-01 00:00:00.000 10 NULL
1 2004-02-08 00:00:00.000 13 3
1 2004-02-15 00:00:00.000 12 -1
2 2004-02-01 00:00:00.000 11 NULL
2 2004-02-08 00:00:00.000 15 4
*/
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
|
|
