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

--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