Calculating change of values between rows

Hello forum.

I have three columns in my table with the following datatypes:

Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal

I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.

The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.

Is it possible to automate the entry in the Change_From_Last_Height column in SQL?

Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?

Thank you.

hi,

your design, to me, is not correct, as you should not store the actual preeceding height or delta, but you should only retrieve it as you already have that value in the "preeceding row"..

so, in my mind, you only need to add a "calculated column" to your projection, responsible for getting the actual preeceding value or the delta..

the design changes to something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.Plants( Id int NOT NULL PRIMARY KEY, Name varchar(10) NOT NULL ); CREATE TABLE dbo.PlantsHeight ( Id int NOT NULL CONSTRAINT fk_PlantHeight$Has$Plant FOREIGN KEY REFERENCES dbo.Plants (Id), Date datetime, Height decimal(12,6) CONSTRAINT pk_PlantsHeight PRIMARY KEY (Id, Date) ); GO INSERT INTO dbo.Plants VALUES ( 1, 'Plant 1' ); INSERT INTO dbo.Plants VALUES ( 2, 'Plant 2' ); INSERT INTO dbo.Plants VALUES ( 3, 'Plant 3' ); INSERT INTO dbo.PlantsHeight VALUES ( 1, '20070101', 10 ); INSERT INTO dbo.PlantsHeight VALUES ( 1, '20070102', 11 ); INSERT INTO dbo.PlantsHeight VALUES ( 1, '20070103', 12 ); INSERT INTO dbo.PlantsHeight VALUES ( 1, '20070104', 13 ); INSERT INTO dbo.PlantsHeight VALUES ( 1, '20070105', 14 ); INSERT INTO dbo.PlantsHeight VALUES ( 2, '20070101', 10 ); INSERT INTO dbo.PlantsHeight VALUES ( 2, '20070102', 10.3 ); INSERT INTO dbo.PlantsHeight VALUES ( 2, '20070103', 11.2 ); INSERT INTO dbo.PlantsHeight VALUES ( 2, '20070104', 11.9 ); INSERT INTO dbo.PlantsHeight VALUES ( 2, '20070105', 12 ); GO SELECT p.Name, ph.Date, ph.Height, (SELECT TOP (1) h.Height FROM dbo.PlantsHeight h WHERE h.Id = ph.Id AND h.Date < ph.Date ORDER by h.Date DESC) AS [SubQuery Preceeding Height], ph.Height - ISNULL((SELECT TOP (1) h.Height FROM dbo.PlantsHeight h WHERE h.Id = ph.Id AND h.Date < ph.Date ORDER by h.Date DESC), ph.Height ) AS [SubQuery Difference] FROM dbo.Plants p JOIN dbo.PlantsHeight ph ON p.Id = ph.Id ORDER BY p.Id, ph.Date; GO DROP TABLE dbo.PlantsHeight, dbo.Plants; --<- Name Date Height SubQuery Preceeding Height SubQuery Difference - -- -- -- - Plant 1 2007-01-01 00:00:00.000 10.000000 NULL 0.000000 Plant 1 2007-01-02 00:00:00.000 11.000000 10.000000 1.000000 Plant 1 2007-01-03 00:00:00.000 12.000000 11.000000 1.000000 Plant 1 2007-01-04 00:00:00.000 13.000000 12.000000 1.000000 Plant 1 2007-01-05 00:00:00.000 14.000000 13.000000 1.000000 Plant 2 2007-01-01 00:00:00.000 10.000000 NULL 0.000000 Plant 2 2007-01-02 00:00:00.000 10.300000 10.000000 0.300000 Plant 2 2007-01-03 00:00:00.000 11.200000 10.300000 0.900000 Plant 2 2007-01-04 00:00:00.000 11.900000 11.200000 0.700000 Plant 2 2007-01-05 00:00:00.000 12.000000 11.900000 0.100000

where the data is retrieved via a simple subquery using a TOP(1) tiebraker ordered by Date in descending order... you could use other semijoin techinque to avoid the top(1), but this is very simple.. and the required data is materialized on the fly without denormalizing the data design, and, should you "modify" an older value, you do not have to worry about modifying successive rows.. the normalization prevents this kind of "errors" that can lead you insane once you have thousands of rows..

regards

|||

Wow! Andrea, you're a life saver!

Thank you! Thank you! Thank you! This problem had me really nervous and you wound up contributing a more intensive solution than I could have possibly asked for.

Once again, thanks a lot, I'll try it out in a moment.